Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Conditional load on Fieldname

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?

1 Solution

Accepted Solutions
MVP
MVP

Re: Conditional load on Fieldname

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;

4 Replies
MVP
MVP

Re: Conditional load on Fieldname

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;

Not applicable

Re: Conditional load on Fieldname

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?

MVP
MVP

Re: Conditional load on Fieldname

You could try something like

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

Not applicable

Re: Conditional load on Fieldname

Thanks, it works like a charm now!

Your help is appreciated.

Community Browser