Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I want to load some fields with dynamic expressions set up through a cvs file based on Condition that filters the Expression field value.
The Excel sample file looks like this:
Expression | FieldAlias | Condition |
IF(TransactionTypeID=1, UnitsX) | SumOfUnitsX | vLoadSalesType1 |
RevenueX*USDxRate | RevenueX_USD | vLoadSalesType1 AND vLoadPriceType1 |
RevenuesY*EURxRate | RevenueY_EUR | vLoadSalesType2 AND vLoadPriceType1 |
The "Condition" field values are variables in the app/doc that depend on other Config settings from a different csv file, which could be either 1 or 0. These variables will be used to filter the rows of Expressions needed in my App. For example, my specific App could not vLoadSalesType2=0 and row 3 (Expression value) should not be loaded.
My idea is that in the LOAD statement I could convert the Expression with ' as ' & FieldAlias as a Fieldname and then load a CONCAT of Fieldname in order to create a variable with a list of fields to be finally loaded in a final LOAD from the actual Data table.
My attempts have been unsuccessful so far and would highly appreciate some light
Hope it makes sense.
Thanks!
Jay
I think I found the solution:
ControlFile:
LOAD
rowno() as Row_Number,
Expression,
FieldAlias,
Condition
FROM ControlFile.csv (txt, codepage is 28591, embedded labels, delimiter is ',', msq);
LET vRowsNumber = fieldvaluecount('FieldAlias');
FOR i = 1 to vRowsNumber
Let vCondition = Peek('Condition',$(i)-1,'ControlFile');
IF $(vCondition) then
Temp: // CONCATENATE assumed next load
LOAD
Expression & ' as ' & FieldAlias as ExpressionField
Resident ControlFile
Where Row_Number=$(i);
ENDIF
NEXT i
Drop table ControlFile;
So now I have my ExpressionField field that I will load with CONCAT() to be used to create a variable for my final DATA load
Jay