How to apply Pick(Match()) statement in a table in Data Load Editor

[MEBG93]

Hello experts,

I need to execute a Pick(match()) statement which retrieves a bunch of expressions from an excel which are meant to be applicated at row-level in a table during the load script and save only the resulting values. For example:

I have one excel which has the following columns:

KPI ID, KPI Name, KPI Expression

So in KPI expression I've got the Pick(match()) statement where Pick(match(KPI ID, '1', '2'),num(Sum(sales), '$#,##0.00'), num(sum(netsales), '$#,##0.00'))

Right now I'm saving KPI Expression as a variable and then I apply it afterwards in the table sheet, but what I want to do is to apply those expressions and save its resulting values in a table when loading the script. So instead of having a table with values such as:

KPI ID, KPI Name, KPI Expression

1, 'Total Sales', num(Sum(sales), '$#,##0.00')

2, 'Net Sales', sum(netsales), '$#,##0.00')

I want to have these resulting values in the KPI Expression column:

KPI ID, KPI Name, KPI Expression

1, Total Sales, $ 123,456.00

2, Net Sales, $ 80,123.00

Thanks in advance

[maxgro · Accepted solution]

// Test data, Sales
Sales:
Load
    floor(rand()*100) as sales,
    floor(rand()*100) as netsales
AutoGenerate 100;

// KPI table, replace this with a read from excel
Excel:
LOAD * INLINE [
KPI ID, KPI Name, KPI Expression
1, 'Total Sales', "NUM(Sum(sales), '$#,##0.00')"
2, 'Net Sales', "NUM(Sum(netsales), '$#,##0.00')"
11, 'Total Sales 2', "NUM(Sum(sales), '$#,##0.00')"
];

// for every row in Excel table
FOR i=0 TO NoOfRows('Excel') -1

    // set a variable with KPI ID
    // and a variable with KPI Expression
    LET vkpi = PEEK('KPI ID', $(i), 'Excel');
    LET vexp = PEEK('KPI Expression', $(i), 'Excel');
    // show the variables
    TRACE vkpi=$(vkpi);
    TRACE vexp=$(vexp);

    // Add a rows to a new table X
    X:
    LOAD
      $(vkpi) AS "KPI ID", // same field of Excel
      $(vexp) as "NEW KPI Expression" // apply the expr. wexp and save the result in "NEW KPI Expressione   field
    RESIDENT Sales;

NEXT

[MEBG93]

Hello @maxgro thanks for your response, but I can't replicate that because there's some expressions that use set analyses, often referencing other tables in the model. So when loading, it pops up this error:

unexpected token: '{', expected one of:'(', 'DISTINCT', etc

table:

load

20 as "KPI ID",

SUM(>>>>>>{<<<<<<<Company={'ABC'}>}SALES) as "NEW KPI Expression"

resident Excel

Also, I have a cell in Excel that contains all the expressions using the Pick(match(ID, 1, 2), sum(sales), sum({<Company={'ABC'}>}sales)) statement. Is there a way of just applying that to a column and saving the results in the script loading process? 

Thanks!

[MEBG93]

@maxgro response is the solution, but I'll have to add that it doesn't work if you're using Set Analysis, because is not possible to apply Set Analysis during Load Script.

If some knows a workaround for this, I'll be greatly appreciated.

Thanks!