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
