populating a string cell with a rule

Post Reply
Brad
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

Post by Brad »

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.
Mark RMBC
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

Post by Mark RMBC »

Hi Brad,

is this an order problem? Have you got ['All sources','Notes Source'] before ['All sources']?

cheers, Mark
tomok
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

Post by tomok »

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:

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'),"");
This syntax is not entirely correct since I have no idea what your cube design is but you should get the idea.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Brad
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

Post by Brad »

['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
Brad
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

Post by Brad »

Mark RMBC wrote: Tue Mar 20, 2018 3:57 pm Hi Brad,

is this an order problem? Have you got ['All sources','Notes Source'] before ['All sources']?

cheers, Mark
Hi Mark
the element 'All sources' is before the 'Notes Source' in the dimension
Brad
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

Post by Brad »

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:

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'),"");
This syntax is not entirely correct since I have no idea what your cube design is but you should get the idea.
I think I responded in the wrong place before so I am pasting it here as well:
['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
Wim Gielis
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

Post by Wim Gielis »

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).
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
Mark RMBC
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

Post by Mark RMBC »

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
Brad
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

Post by Brad »

Wim 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).
Thanks Wim never used the Trace before. when I right click the note cell it is reading the note which it interprets as 0.0000
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?
Wim Gielis
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

Post by Wim Gielis »

Brad wrote: Tue Mar 20, 2018 5:12 pmI 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.
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
Brad
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

Post by Brad »

Wim Gielis wrote: Tue Mar 20, 2018 9:02 pm
Brad wrote: Tue Mar 20, 2018 5:12 pmI 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.
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.
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:
['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',''))));
Post Reply