Closed wimgielis closed 3 years ago
Hi,
Please try to change
value = float(entries[3])
To
value = float(entries[3] or 0)
Im guessing that you read None from the source file and TM1py is trying to add None to a float value. The or operation takes 0 if the value is "implicitly false" (in python None, 0, empty lists and empty strings are implicitly False)
Thank you Marius but I cannot get the Increment=True to work. Here's my code and the input file as well, should you have the time to look at it. In the code I have 6 parts where I add a comment/question/doubt, if possible any insight on that would be greatly appreciated. They are Wim_01, Wim_02, ..., Wim_06.
The code first does a zero out, then loading incrementally lines from a text file. Text file looks like this:
day / project / activity / number of hours
07-01 Project_A Activity_1 2 07-01 Project_B Activity_2 3.5 07-01 Project_A Activity_2 1 07-01 Project_A Activity_1 0.5
This input file has CRLF between lines and TAB between fields. Dot is the decimal separator.
Ultimately I would require 2.5 hours on July 1, Project_A, Activity_1 (adding up lines 1 and 4). The cellset that is created from the file, is fine though.
Error received:
Exception has occurred: TypeError
unsupported operand type(s) for +: 'float' and 'NoneType'
File "C:\Users\WGielis\Python testing\test1.py", line 94, in
load_to_BOM_activity
tm1.cubes.cells.write(cube_name=cube, cellset_as_dict=cellset,
increment=True)
File "C:\Users\WGielis\Python testing\test1.py", line 97, in
from TM1py.Services import TM1Service from TM1py.Utils.Utils import build_pandas_dataframe_from_cellset import pandas as pd from TM1py.Utils import Utils
def load_to_BOM_activity():
# 1. zeroout of existing data
#
=============================================================================================================
#
=============================================================================================================
# TM1 connection settings
ADDRESS = '**.***.**.***'
USER = '*****'
PWD = '*****'
PORT = 8001
SSL = True
cube = "Activity"
#
=============================================================================================================
#
=============================================================================================================
tm1 = TM1Service(address=ADDRESS, port=PORT, user=USER, password=PWD,
namespace='', gateway='', ssl=SSL)
content = tm1.cubes.cells.execute_view('z_ADMIN_PARAM', 'Month for
input', private=False)
# Wim_01: I find the below rather ugly:
# mth = content[('[Z_ADMIN_PARAM].[Z_ADMIN_PARAM].[Current month for
input]', '[VAR_TXT].[VAR_TXT].[STR_VAR1]')]['Value']
input]', '[VAR_TXT].[VAR_TXT].[STR_VAR1]')]['Value']
# I like this approach better:
df = Utils.build_pandas_dataframe_from_cellset(content)
mth = df.iat[0,0]
yr = df.iat[1,0]
# Wim_02: I don't really like the double {{ }} but I think it's needed
when I want to insert variables using {0}, {1}, ... mdx = """ SELECT NON EMPTY {{[Periode].[{0}].Children}} {{TM1FilterByLevel( [Activity].Members, 0 )}} {{TM1FilterByLevel( Descendants( [Client].[TOT_CLIENT]), 0)}} ON ROWS, NON EMPTY {{[Indic].[Hours]}} ON COLUMNS FROM {1} WHERE ([Vision].[Actual], [Ressource].[WGI], [Exercice].[{2}]) """.format(mth, cube, yr)
# Wim_03: it's needed that we use an admin account for the zeroout ?
# I could also create a dataframe, change the values to 0 and write
back to the cube.
tm1.cells.clear_with_mdx(cube=cube, mdx=mdx)
# 2. loading of new data
#
=============================================================================================================
#
=============================================================================================================
# New TM1 connection settings ==> admin user not used anymore
USER = '***'
PWD = '***'
# Wim_04: sometimes we need backslash, sometimes forward slash, is that
correct ? INPUT_FILE = r'D:_AR\data.csv' INPUT_FILE_2 = r'D:/_AR/data.csv'
#
=============================================================================================================
#
=============================================================================================================
tm1 = TM1Service(address=ADDRESS, port=PORT, user=USER, password=PWD,
namespace='', gateway='', ssl=SSL)
# Build cellset from file
cellset = {}
# Wim_05: I removed the header line (1 line) from the file but actually
there is one
with open(INPUT_FILE, "r") as file:
# Read coordinates and values from each line
for line in file:
# first_line = file.readline()
entries = line.split("\t")
coordinates = ("Actual", entries[2], entries[1], "WGI",
str(yr), entries[0], "Hours")
value = float(entries[3] or 0)
cellset[coordinates] = value
# tm1.cubes.cells.write_values(cube, cellset)
# print(cellset)
tm1.cubes.cells.write(cube_name=cube, cellset_as_dict=cellset,
increment=True)
# Wim_06: I was playing around with a dataframe as well, to group by
# but I could not make it work
# input file has CRLF between lines and tab between fields
# # df = pd.read_csv(INPUT_FILE_2, sep="\r\n", header=0)
# df = pd.read_csv(INPUT_FILE_2, sep='\t', lineterminator='\n',
header=0)
if name == "main": load_to_BOM_activity()
Best regards / Beste groeten,
Wim Gielis MS Excel MVP 2011-2014 https://www.wimgielis.com http://www.wimgielis.be
Op wo 21 jul. 2021 om 02:36 schreef Marius Wirtz @.***>:
Hi,
Please try to change
value = float(entries[3])
To
value = float(entries[3] or 0)
Im guessing that you read None from the source file and TM1py is trying to add None to a float value. The or operation takes 0 if the value is "implicitly false" (in python None, 0, empty lists and empty strings are implicitly False)
Get Outlook for Androidhttps://aka.ms/AAb9ysg
From: Wim Gielis @.> Sent: Wednesday, July 21, 2021 2:17:43 AM To: cubewise-code/tm1py @.> Cc: Subscribed @.***> Subject: [cubewise-code/tm1py] How to load to cube and increment (#574)
Describe what did you try to do with TM1py I want to load the contents of a small text file (100 lines) into a TM1 cube. The cellset works and can be loaded in the cube. However, duplicate lines will overwrite, instead of increment.
I also tried by reading the CSV file in a dataframe and do a Group by, but I did not succeed. The group by needs to add up the hours (last column) over multiple other columns (basically, the 3 other columns). So I gave up on the group by and focused on the cellset with increment = True in "tm1.cubes.cells.write"
The error I get is: TypeError: unsupported operand type(s) for +: 'float' and 'NoneType' Somehow the incrementing part is causing in issue in my code.
Input file contents to load: 07-01 Project A Consultancy 8 07-02 Project B Consultancy 9
Code:
from TM1py.Services import TM1Service from TM1py.Utils.Utils import build_pandas_dataframe_from_cellset import pandas as pd from TM1py.Utils import Utils
def load_activity():
1. zeroout of existing data
this is okay
2. loading of new data
ADDRESS = '...' USER = '' PWD = '' PORT = 8001 SSL = True
INPUT_FILE = r'D:\Inputfile.csv'
tm1 = TM1Service(address=ADDRESS, port=PORT, user=USER, password=PWD, namespace='', gateway='', ssl=SSL)
Build cellset from file
cellset = {}
with open(INPUT_FILE, "r") as file: for line in file: entries = line.split("\t") coordinates = ("Actual", entries[2], entries[1], "WGI", "2021", entries[0], "Hours") value = float(entries[3]) cellset[coordinates] = value
tm1.cubes.cells.write_values(cube, cellset)
print(cellset)
tm1.cubes.cells.write(cube_name=cube, cellset_as_dict=cellset, increment=True)
if name == "main": load_activity()
Thanks a lot !
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub< https://github.com/cubewise-code/tm1py/issues/574>, or unsubscribe< https://github.com/notifications/unsubscribe-auth/AHLDPJCNKRDTLPXK3VCOVRTTYYG2PANCNFSM5AW4EXTQ
.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/cubewise-code/tm1py/issues/574#issuecomment-883797168, or unsubscribe https://github.com/notifications/unsubscribe-auth/AEDHULOJTKQLJLRGZM6WDXDTYYJCLANCNFSM5AW4EXTQ .
@wimgielis The increment
functionality doesn't work the same way as it is in TM1.
The write
method creates a cellset (dictionary) with the data that you have in your file and then build an MDX using this cellset. The MDX gets queried and then the values that got obtained will be added with the values from your new cellset.
We don't perform a cellwise increment here, its merely an addition of two table like structure. If you have duplicate records in your source file, the latest value in the file will be added with the MDX obtained result and loaded into the cube.
Example:
Your source file: a,b,c,9 a,b,c,4
Your cube: a,b,c,10
After the calling the write
method with increment
set as True, your cube would look like this:
a,b,c,14
Okay thanks for the information. How should I proceed to mimick CellIncrementN rather than have CellPutN, for the duplicate lines in my file ?
All the best,
Wim
@MariusWirtz I couldn't spend time to understand the usage of None
here. But I think this is the one causing the issue for Wim.
https://github.com/cubewise-code/tm1py/blob/57ff1d9e42b956f6eacd7bae00843ed2124ba383/TM1py/Services/CellService.py#L705
values = [5, 3.34, 4]
current_values = [3.8, 0, 5]
values = (x + (y or None) for x, y in zip(values, current_values))
print(list(values))
TypeError: unsupported operand type(s) for +: 'float' and 'NoneType'
@rkvinoth thanks for looking into this!
To me, this looks like a bug. it would be more logical if it said :
values = (x + (y or 0) for x, y in zip(values, current_values))
or even better:
values = ((x or 0) + (y or 0) for x, y in zip(values, current_values))
@wimgielis Could you please change this line in your local TM1py CellService file in the lib/site-packages/tm1py/Services/CellService.py file and let us know if this fixes the issue?
@wimgielis happy to check out the code but it's almost impossible to re-use when it comes from an email reply. Can you post it again in this thread and mark it as code with markdown (triple backticks at the top and triple backticks at the bottom). Then I can copy and paste it into PyCharm.
@wimgielis happy to check out the code but it's almost impossible to re-use when it comes from an email reply. Can you post it again in this thread and mark it as code with markdown (triple backticks at the top and triple backticks at the bottom). Then I can copy and paste it into PyCharm.
Sure, I will do this. For now, I see that it is not working for me. I can see in the stack trace that my new code was used.
@wimgielis
I would suggest that you perform a groupby
for all the columns to consolidate the duplicate combinations.
df = df.groupby(by=["Periode", "Client", "Activity"]).sum("Hours").reset_index()
Then use the increment
argument to load it into the cube.
If things don't workout please attach the source file here.
Here's my code and the input file as well, should you have the time to look at it. In the code I have 6 parts where I add a comment/question/doubt, if possible any insight on that would be greatly appreciated. They are Wim_01, Wim_02, ..., Wim_06.
The code first does a zero out, then loading incrementally lines from a text file. Text file looks like this:
day / project / activity / number of hours
07-01 Project_A Activity_1 2 07-01 Project_B Activity_2 3.5 07-01 Project_A Activity_2 1 07-01 Project_A Activity_1 0.5
This input file has CRLF between lines and TAB between fields. Dot is the decimal separator.
Ultimately I would require 2.5 hours on July 1, Project_A, Activity_1 (adding up lines 1 and 4). The cellset that is created from the file, is fine though.
Error received:
Exception has occurred: TypeError
unsupported operand type(s) for +: 'float' and 'NoneType'
File "C:\Users\WGielis\Python testing\test1.py", line 94, in load_to_BOM_activity
tm1.cubes.cells.write(cube_name=cube, cellset_as_dict=cellset, increment=True)
File "C:\Users\WGielis\Python testing\test1.py", line 97, in
from TM1py.Services import TM1Service
from TM1py.Utils.Utils import build_pandas_dataframe_from_cellset
import pandas as pd
from TM1py.Utils import Utils
def load_to_BOM_activity():
# 1. zeroout of existing data
# =============================================================================================================
# START of parameters and settings
# =============================================================================================================
# TM1 connection settings
ADDRESS = '**.***.**.***'
USER = '*****'
PWD = '*****'
PORT = 8001
SSL = True
cube = "Activity"
# =============================================================================================================
# END of parameters and settings
# =============================================================================================================
tm1 = TM1Service(address=ADDRESS, port=PORT, user=USER, password=PWD, namespace='', gateway='', ssl=SSL)
content = tm1.cubes.cells.execute_view('z_ADMIN_PARAM', 'Month for input', private=False)
# Wim_01: I find the below rather ugly:
# mth = content[('[Z_ADMIN_PARAM].[Z_ADMIN_PARAM].[Current month for input]', '[VAR_TXT].[VAR_TXT].[STR_VAR1]')]['Value']
# yr = content[('[Z_ADMIN_PARAM].[Z_ADMIN_PARAM].[Current year for input]', '[VAR_TXT].[VAR_TXT].[STR_VAR1]')]['Value']
# I like this approach better:
df = Utils.build_pandas_dataframe_from_cellset(content)
mth = df.iat[0,0]
yr = df.iat[1,0]
# Wim_02: I don't really like the double {{ }} but I think it's needed when I want to insert variables using {0}, {1}, ...
mdx = """
SELECT
NON EMPTY {{[Periode].[{0}].Children}} * {{TM1FilterByLevel( [Activity].Members, 0 )}} * {{TM1FilterByLevel( Descendants( [Client].[TOT_CLIENT]), 0)}} ON ROWS,
NON EMPTY {{[Indic].[Hours]}} ON COLUMNS
FROM {1}
WHERE ([Vision].[Actual], [Ressource].[WGI], [Exercice].[{2}])
""".format(mth, cube, yr)
# Wim_03: it's needed that we use an admin account for the zeroout ?
# I could also create a dataframe, change the values to 0 and write back to the cube.
# Then we would not need an admin account ?
tm1.cells.clear_with_mdx(cube=cube, mdx=mdx)
# 2. loading of new data
# =============================================================================================================
# START of parameters and settings
# =============================================================================================================
# New TM1 connection settings ==> admin user not used anymore
USER = '***'
PWD = '***'
# Wim_04: sometimes we need backslash, sometimes forward slash, is that correct ?
INPUT_FILE = r'D:\_AR\data.csv'
INPUT_FILE_2 = r'D:/_AR/data.csv'
# =============================================================================================================
# END of parameters and settings
# =============================================================================================================
tm1 = TM1Service(address=ADDRESS, port=PORT, user=USER, password=PWD, namespace='', gateway='', ssl=SSL)
# Build cellset from file
cellset = {}
# Wim_05: I removed the header line (1 line) from the file but actually there is one
# so ideally I would like to know how to deal with a 1-line header
with open(INPUT_FILE, "r") as file:
# Read coordinates and values from each line
for line in file:
# first_line = file.readline()
entries = line.split("\t")
coordinates = ("Actual", entries[2], entries[1], "WGI", str(yr), entries[0], "Hours")
# print(entries[3])
value = float(entries[3] or 0)
cellset[coordinates] = value
# tm1.cubes.cells.write_values(cube, cellset)
# print(cellset)
tm1.cubes.cells.write(cube_name=cube, cellset_as_dict=cellset, increment=True)
# Wim_06: I was playing around with a dataframe as well, to group by
# but I could not make it work
# input file has CRLF between lines and tab between fields
# # df = pd.read_csv(INPUT_FILE_2, sep="\r\n", header=0)
# df = pd.read_csv(INPUT_FILE_2, sep='\t', lineterminator='\n', header=0)
# # df = df.groupby("Periode", "Client", "Activity").sum("Hours")
if __name__ == "__main__":
load_to_BOM_activity()
If you are performing a zero out of the cube, then a simple groupby
and calling the write
method should work for you.
That's what I tried to do but failed. Please refer to a couple of lines in the code I supplied above in code tags. The file has a header (1 line) but also without I stumbled upon errors when doing even the simplest things. I am still a beginner with Python :-)
Best regards / Beste groeten,
Wim Gielis MS Excel MVP 2011-2014 https://www.wimgielis.com http://www.wimgielis.be
Op wo 21 jul. 2021 om 14:19 schreef Vinoth Kumar Ravi < @.***>:
If you are performing a zero out of the cube, then a simple groupby and calling the write method should work for you.
— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/cubewise-code/tm1py/issues/574#issuecomment-884145609, or unsubscribe https://github.com/notifications/unsubscribe-auth/AEDHULNN5XRUD5SH7DZXJQDTY23M3ANCNFSM5AW4EXTQ .
Giving you an example.
Copy the below table: <html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">
a | b | c | h -- | -- | -- | -- ab | df | we | 2 ab | df | we | 5 ab | er | qw | 2
Describe what did you try to do with TM1py I want to load the contents of a small text file (100 lines) into a TM1 cube. The cellset works and can be loaded in the cube. However, duplicate lines will overwrite, instead of increment.
I also tried by reading the CSV file in a dataframe and do a Group by, but I did not succeed. The group by needs to add up the hours (last column) over multiple other columns (basically, the 3 other columns). So I gave up on the group by and focused on the cellset with increment = True in "tm1.cubes.cells.write"
The error I get is: TypeError: unsupported operand type(s) for +: 'float' and 'NoneType' Somehow the incrementing part is causing in issue in my code.
Input file contents to load: 07-01 Project A Consultancy 8
07-02 Project B Consultancy 9
Code:
Thanks a lot !