Preparing Expressions To Load based on Condition field on a CSV

jaygarcia — Mon, 24 Sep 2018 17:23:21 GMT

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

Re: Preparing Expressions To Load based on Condition field on a CSV

jaygarcia — Tue, 25 Sep 2018 10:20:36 GMT

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

topic Re: Preparing Expressions To Load based on Condition field on a CSV in QlikView

Preparing Expressions To Load based on Condition field on a CSV

Re: Preparing Expressions To Load based on Condition field on a CSV