I knew that but i wonder why the function is available in the expression editor.
It should not be available if you cannot use it.
Do you know how i can in an expression use a function to make a select of a description in an other table that is not linked to the model ( an orphan table).
Set analysis doesn't work because of the orphan table.
I can't resolve it in the script.
Here is my problem:
I got a customer category table and a sales table.
I've created a pivot table with the dimension category_id and with an input field we can truncate the category_id from 1 to 10 digit.
I would like to get the description of the trucated id and so make a kind of lookup in an orphan table.
Category_id item_id Amount
311 A 100
31 B 150
If i chose in my input field only 1 position my category_id dimension is calculated with left(category_id,1)
so my pivot table say :
the thing is that i would like to get de description of category_id 3 and it is available in an orphan table Category.
So my idea was to add in my calculated dimension an applymap based on orphan table Category.
If your fields in the orphan table have only unique values for description and category id (this is important! - but I assume that's the case in your case), you could use an expression like this to retrieve the description from an orphan table.
=Fieldvalue('Category.Desc',FieldIndex('Category.CategoryID', left(category_id,1) ))
where Category.Desc resp. Category.CategoryID are your fieldnames from the ophan table and the second parameter to Fieldindex should return the truncated number you want to look up. I assumed left(category_id,1), but you might have to adjust this to your needs.
Hope this helps,
I have an Orphan table
Min Max 1st Rate 2nd Rate 0 500 5,00% 4,00% 500,01 1000 4,75% 3,80% 1000,01 1500 4,51% 3,61% 1500,01 2000 4,29% 3,43% 2000,01 2500 4,07% 3,26% 2500,01 12000 3,87% 3,10% 12000,01 15000 3,68% 2,94% 15000,01 20000 3,49% 2,79% 20000,01 100000 3,32% 2,65%
I then have a sum of Sales calculation that i need to evaluate against this table and return 1st and 2nd rate, i havn't a clue where to start, please can anyone assist?