TM1 Forum

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.

Hi Brad,

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

cheers, Mark

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.

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

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

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

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

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

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?

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.

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',''))));