Solved: Conditional load on Fieldname - Qlik Community

Report Inappropriate Content · ‎2016-01-12

Hi,

I want to load certain fields based on their fieldname.

I tried to do this with an if statement but I don't get it toe work.

It should be something like this:

LOAD

if(fieldname contains '*K01*') as fieldname

Does anyone has an idea how to apply conditional load based on fieldnames?

swuehl · ‎2016-01-12

And you don't know the field names upfront?

Maybe there are better solutions to what you are trying to achieve at the end, for creating a filtered list of field names to load, you can try something like

// Only a dummy table to have something to play with
TABLE:
LOAD * INLINE [
    Dimension, Metric 1, Metric 2
    1, 1, 10
    2, 2, 9
    3, 3, 8
    4, 4, 7
    5, 5, 6
    6, 6, 5
    7, 7, 4
    8, 8, 3
    9, 9, 2
    10, 10, 1
];

// Create a field with all field names
FIELDS:
CROSSTABLE (FieldName, Value) LOAD
1 as Dummy, *
Resident TABLE
where RecNo() =1;


DROP FIELDS Dummy, Value;

// Create a filtered list of the field names, embedded in [..] to handle spaces etc.
CONCAT:
LOAD '[' & CONCAT(If (FieldName Like 'Metric*', FieldName),'], [') & ']' as FilteredFields
RESIDENT FIELDS;

// Create a variable from that filtered table value
Let vFilteredFields = Peek('FilteredFields',0,'CONCAT');

// Finally load your filtered table from source, instead of a resident TABLE LOAD, you can load your data directly FROM your source here and in the FIELDS table
LOAD $(vFilteredFields) RESIDENT TABLE;

//Clean up
DROP TABLE TABLE, FIELDS, CONCAT;

View solution in original post

swuehl · ‎2016-01-12

And you don't know the field names upfront?

Maybe there are better solutions to what you are trying to achieve at the end, for creating a filtered list of field names to load, you can try something like

// Only a dummy table to have something to play with
TABLE:
LOAD * INLINE [
    Dimension, Metric 1, Metric 2
    1, 1, 10
    2, 2, 9
    3, 3, 8
    4, 4, 7
    5, 5, 6
    6, 6, 5
    7, 7, 4
    8, 8, 3
    9, 9, 2
    10, 10, 1
];

// Create a field with all field names
FIELDS:
CROSSTABLE (FieldName, Value) LOAD
1 as Dummy, *
Resident TABLE
where RecNo() =1;


DROP FIELDS Dummy, Value;

// Create a filtered list of the field names, embedded in [..] to handle spaces etc.
CONCAT:
LOAD '[' & CONCAT(If (FieldName Like 'Metric*', FieldName),'], [') & ']' as FilteredFields
RESIDENT FIELDS;

// Create a variable from that filtered table value
Let vFilteredFields = Peek('FilteredFields',0,'CONCAT');

// Finally load your filtered table from source, instead of a resident TABLE LOAD, you can load your data directly FROM your source here and in the FIELDS table
LOAD $(vFilteredFields) RESIDENT TABLE;

//Clean up
DROP TABLE TABLE, FIELDS, CONCAT;

Report Inappropriate Content · ‎2016-01-13

I have an additional question about your script.

Your suggestion is a smart solution and it works. So thanks for that.

But I need a little more complex add-on because I like to filter the fieldnames on their text 'format' instead of the text itself.

A simplified example of the targeted fieldnames look like these:

K01.01loremipsum

C12.21loremipsum

S03.12loremipsum

I would like to filter on the first 6 characters of the fieldname, where:

the first character is a letter,

the 2nd, 3rd, 5th and 6th is a number

the 3rd is a 'period'

Is it possible to add this to the filter commands your wrote on row 29?

swuehl · ‎2016-01-13

You could try something like

CONCAT( If (isText(Left(FieldName,1)) AND isNum(Mid(FieldName,2,5), FieldName), ....

Report Inappropriate Content · ‎2016-01-13

Thanks, it works like a charm now!

Your help is appreciated.