- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to apply Pick(Match()) statement in a table in Data Load Editor
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
- Subscribe by Topic:
-
Data Load Editor
-
Developers
-
expression
-
filter
-
General Question
-
Script
-
Variables
-
Visualization
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
// 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
// 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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!