How to apply pick(match()) statement into a table during the script load

[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 applied 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({<Period={'2022'}>}sales), '$#,##0.00'), num(sum(netsales), '$#,##0.00'))

And right now i tried to apply a possible solution taken from a previous post:

// 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 Expressios"   field
    RESIDENT Sales;

NEXT

The thing is that because I have set analyses spread all over the pick(match()) statement, the script pops 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

What I want for the resulting table is to from this:

KPI ID, KPI Name, KPI Expression

1, Total Sales 2022, Pick(match(KPI ID, '1', '2'),num(Sum({<Period={'2022'}>}sales), '$#,##0.00'), num(sum(netsales), '$#,##0.00'))

2, Net Sales, 

To look like this:

KPI ID, KPI Name, KPI Expression

1, Total Sales 2022, $123.456,78

2, Net Sales, $234.567,77

Thanks in advance

[vinieme12 · Accepted solution]

Set Analysis is for charts  and not  for data load editor

Main:

Load  

1 as KPI_ID

,'Total Sales'  as KPIName

,sum(Sales) as KPI Expression

Resident SomeTable;

Concatenate (Main)

Load 

2 as KPI_ID

,'Net Sales'  as KPIName

,sum(netSales) as KPI Expression

Resident SomeTable;

[MEBG93]

Thanks for the clarification @vinieme12 . Is there a possible workaround for this?

[vinieme12]

no