Preparing Expressions To Load based on Condition field on a CSV

[jaygarcia]

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

[jaygarcia]

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