Load Fields only if they exist

m_torabi — Wed, 25 Nov 2020 16:16:04 GMT

In Qlikview, I want to Load a table with the name of fields that I already know: field1, field2, ....

The table I am reading from may or may not contain on of the above fields. I want to set a predefined value (for example 0) for a field if it doesn't exist.

Could you add some comment and tell me how can I improve my code?

Example:

Source table:

My list of fields:

Field1
Field2
Field3
Field4

My desired result:

I wrote a code for it:

  InlineTable: LOAD * INLINE [ Field1, Field2, OtherField1, OtherField2 1, 4, 7, 10 2, 5, 8, 11 3, 6, 9, 12 ]; SignTable: load if (FieldNumber('Field1','InlineTable')=0,0,Field1) as Field1, if (FieldNumber('Field2','InlineTable')=0,0,Field2) as Field2, if (FieldNumber('Field3','InlineTable')=0,0,Field3) as Field3 resident InlineTable; drop table InlineTable;

but it gives an error:

Re: Load Fields only if they exist

sunny_talwar — Mon, 26 Mar 2018 11:30:41 GMT

Try this

InlineTable:

LOAD * INLINE [

Field1, Field2, OtherField1, OtherField2

1, 4, 7, 10

2, 5, 8, 11

3, 6, 9, 12

];

LET vField1 = if(FieldNumber('Field1','InlineTable') = 0, 0, 'Field1');

LET vField2 = if(FieldNumber('Field2','InlineTable') = 0, 0, 'Field2');

LET vField3 = if(FieldNumber('Field3','InlineTable') = 0, 0, 'Field3');

SignTable:

LOAD $(vField1) as Field1,

$(vField2) as Field2,

$(vField3) as Field3

Resident InlineTable;

DROP Table InlineTable;

topic Load Fields only if they exist in QlikView

Load Fields only if they exist

Re: Load Fields only if they exist