populating a string cell with a rule
-
- Posts: 6
- Joined: Tue Mar 20, 2018 11:48 am
- OLAP Product: TM1 10.2
- Version: TM1 10.2
- Excel Version: Excel 2010
populating a string cell with a rule
I have a cube with data from differnt sources. The "source" dim has 6 elements the 6th one is the allsources which I have a rule that
['All sources']=If(['source1']<>0,['source1'],If(['source2']<>0,['source2'],If(['source3']<>0,['source3'], ...],0))));
The code above works fine and populates Allsource as I want. I also need to identify the data from which source if comes, so I tried
['All sources','Notes Source']=If(['source1']<>0,"source1",If(['source2']<>0,"source2",ect..
This didnt work so I tried
['All sources','Notes Source']=S:If(['source1']<>0,DB('fdi_cty',!FDI,!Direction,!FDI Composition,!Years,!Country,'Notes'),"");
this also didnt work. I dont know what else to try and I could find anything relating to it. your help is needed and appreciated.
['All sources']=If(['source1']<>0,['source1'],If(['source2']<>0,['source2'],If(['source3']<>0,['source3'], ...],0))));
The code above works fine and populates Allsource as I want. I also need to identify the data from which source if comes, so I tried
['All sources','Notes Source']=If(['source1']<>0,"source1",If(['source2']<>0,"source2",ect..
This didnt work so I tried
['All sources','Notes Source']=S:If(['source1']<>0,DB('fdi_cty',!FDI,!Direction,!FDI Composition,!Years,!Country,'Notes'),"");
this also didnt work. I dont know what else to try and I could find anything relating to it. your help is needed and appreciated.
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: populating a string cell with a rule
Hi Brad,
is this an order problem? Have you got ['All sources','Notes Source'] before ['All sources']?
cheers, Mark
is this an order problem? Have you got ['All sources','Notes Source'] before ['All sources']?
cheers, Mark
-
- MVP
- Posts: 2831
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: populating a string cell with a rule
When you are writing string rules you have to use the full DB syntax on the right-hand side, not the short-hand [..] notation. Something like:
This syntax is not entirely correct since I have no idea what your cube design is but you should get the idea.
Code: Select all
['All sources','Notes Source']=S:If(DB(CubeName, !Dim1, !Dim2,... !Dimx, 'source1')<>0,DB('fdi_cty',!FDI,!Direction,!FDI Composition,!Years,!Country,'Notes'),"");
-
- Posts: 6
- Joined: Tue Mar 20, 2018 11:48 am
- OLAP Product: TM1 10.2
- Version: TM1 10.2
- Excel Version: Excel 2010
Re: populating a string cell with a rule
['All sources','Notes Source']=If(['source1']<>0,"source1",If(['source2']<>0,"source2",ect..
This I would think is the simplest solution that if source1 isnt 0 then plug in the string "source1"
but the sytax I used didnt work. so then I put the name ex. "source1" in the note element of the dim cube for all valuees insource1
then put the code
['All sources','Notes Source']=S:If(DB('fdi_cty',!dim1,!dim2,'Source1',!Years,!Country,!Value)<>0,
DB('fdi_cty',!Dim,!Dim2,'All sources',!Years,!Country,'Note'),"");
this last code does save but with no results
This I would think is the simplest solution that if source1 isnt 0 then plug in the string "source1"
but the sytax I used didnt work. so then I put the name ex. "source1" in the note element of the dim cube for all valuees insource1
then put the code
['All sources','Notes Source']=S:If(DB('fdi_cty',!dim1,!dim2,'Source1',!Years,!Country,!Value)<>0,
DB('fdi_cty',!Dim,!Dim2,'All sources',!Years,!Country,'Note'),"");
this last code does save but with no results
-
- Posts: 6
- Joined: Tue Mar 20, 2018 11:48 am
- OLAP Product: TM1 10.2
- Version: TM1 10.2
- Excel Version: Excel 2010
Re: populating a string cell with a rule
I think I responded in the wrong place before so I am pasting it here as well:tomok wrote: ↑Tue Mar 20, 2018 4:08 pm When you are writing string rules you have to use the full DB syntax on the right-hand side, not the short-hand [..] notation. Something like:
This syntax is not entirely correct since I have no idea what your cube design is but you should get the idea.Code: Select all
['All sources','Notes Source']=S:If(DB(CubeName, !Dim1, !Dim2,... !Dimx, 'source1')<>0,DB('fdi_cty',!FDI,!Direction,!FDI Composition,!Years,!Country,'Notes'),"");
['All sources','Notes Source']=If(['source1']<>0,"source1",If(['source2']<>0,"source2",ect..
This I would think is the simplest solution that if source1 isnt 0 then plug in the string "source1"
but the sytax I used didnt work. so then I put the name ex. "source1" in the note element of the dim cube for all values in 'Source1'
then put the code
['All sources','Notes Source']=S:If(DB('fdi_cty',!dim1,!dim2,'Source1',!Years,!Country,!Value)<>0,
DB('fdi_cty',!Dim,!Dim2,'All sources',!Years,!Country,'Note'),"");
I put the S: because I want it to return a string but the if statement is looking at a number. thats another thing that confuses me.
this last code does save but with no results
-
- MVP
- Posts: 3105
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: populating a string cell with a rule
Hi
For one thing, TM1 uses ' where Excel uses "
So make sure you got these right.
If you don't have syntax errors but the result is incorrect, use the 'Trace calculation' functionality (right-click a cube cell in the cube viewer).
For one thing, TM1 uses ' where Excel uses "
So make sure you got these right.
If you don't have syntax errors but the result is incorrect, use the 'Trace calculation' functionality (right-click a cube cell in the cube viewer).
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: populating a string cell with a rule
Hi Brad,
I didn't mean the order of the elements in the dimension. I meant the order of the statements in the rule itself.
So I was asking if
['All sources']=If(['source1']<>0,['source1'],If(['source2']<>0,['source2'],If(['source3']<>0,['source3'], ...],0))));
came before or after
'All sources','Notes Source']=S:If(['source1']<>0,DB('fdi_cty',!FDI,!Direction,!FDI Composition,!Years,!Country,'Notes'),"");
But looking at your subsequent replies it appears your problem is something else altogether!
cheers, Mark
I didn't mean the order of the elements in the dimension. I meant the order of the statements in the rule itself.
So I was asking if
['All sources']=If(['source1']<>0,['source1'],If(['source2']<>0,['source2'],If(['source3']<>0,['source3'], ...],0))));
came before or after
'All sources','Notes Source']=S:If(['source1']<>0,DB('fdi_cty',!FDI,!Direction,!FDI Composition,!Years,!Country,'Notes'),"");
But looking at your subsequent replies it appears your problem is something else altogether!
cheers, Mark
-
- Posts: 6
- Joined: Tue Mar 20, 2018 11:48 am
- OLAP Product: TM1 10.2
- Version: TM1 10.2
- Excel Version: Excel 2010
Re: populating a string cell with a rule
Thanks Wim never used the Trace before. when I right click the note cell it is reading the note which it interprets as 0.0000Wim Gielis wrote: ↑Tue Mar 20, 2018 5:14 pm Hi
For one thing, TM1 uses ' where Excel uses "
So make sure you got these right.
If you don't have syntax errors but the result is incorrect, use the 'Trace calculation' functionality (right-click a cube cell in the cube viewer).
therefore it does nothing because its looking for a number.
If I remove the S: then it wont save due to an invalid numeric expression
So how do I get it to check for a number then return a string?
-
- MVP
- Posts: 3105
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: populating a string cell with a rule
S: or nothing ?
That's not a difficult question. If you write a rule for a String type cell, you must include the S: or TM1 will complain when you save the rules.
Checking a number:
Why not ? You can e.g. return the string 'HELLO' if a certain numeric cell is equal to 0, and return 'GOODBYE' if the cell value equals 1.
I don't see any difficulty with that.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 6
- Joined: Tue Mar 20, 2018 11:48 am
- OLAP Product: TM1 10.2
- Version: TM1 10.2
- Excel Version: Excel 2010
Re: populating a string cell with a rule
Thanks for all your help it works now, the problem was I was missing the obvious by not putting the 'Value' in the code. Below is the code that worked:Wim Gielis wrote: ↑Tue Mar 20, 2018 9:02 pmS: or nothing ?
That's not a difficult question. If you write a rule for a String type cell, you must include the S: or TM1 will complain when you save the rules.
Checking a number:
Why not ? You can e.g. return the string 'HELLO' if a certain numeric cell is equal to 0, and return 'GOODBYE' if the cell value equals 1.
I don't see any difficulty with that.
['All sources','Notes Source']=S:If(['source1',''Value']<>0,'source1',If(['source2','Value']<>0,'source2',
If(['source3','Value']<>0,'source3',If(['source4','Value']<>0,'source4',''))));