Controlling Fields Using an External File

[m_perreault]

Hi all,

I have a QVD File with roughly 100 fields and I have a request that users want to be able to control which fields are loaded using an external file like shown below.

Field	Include
Field1	Y
Field2	N
Field3	Y
Field4	Y

Does anyone know how this could be accomplished?

Thanks,

Mark

[jheasley]

Mark - what is the use case for including/excluding data? I have had similar requests from my end users, and we found some better solutions that allowed us to load all data into the app everytime.

[m_perreault]

The use case for this is in addition to loading data into the app, we are also utilizing a STORE statement to load the table into a CSV.  The users wish to view a table with all 100 columns in excel in addition to in Qlik.  As a result they feel it would be beneficial to be able to control fields using an external file to allow for quick exports.

[maxgro · Accepted solution]

you can set a variable with the load statement

and then run the load (variable)

FieldsToLoad:

LOAD

    concat(Field, chr(10) & ', ') as FieldToLoad

FROM

    [https://community.qlik.com/thread/300490]

    (html, codepage is 1252, embedded labels, table is @1)

Where Include = 'Y'

;

// variable with load statement (vload)

SET v = peek('FieldToLoad');

LET vload = 'LOAD ' & $(v) & ' from file.txt (txt, codepage is 1252, embedded labels, delimiter is '','', msq);';

trace $(vload);

// load the file

$(vload);

[m_perreault]

Work's great, very clever!

[m_perreault]

One question however.  Even thought the script will load without errors Qlik Recognizes the $(vload) portion of the script as a typo/error and places an exclamation point next to the section name.  Is there a way to remove this so as to not confuse users?

[m_perreault]

This can be achieved by changing the variable to not have the Load Statement

LET vload =  $(v) & ' FROM [lib://x\x.qvd]

(qvd) ;';

Load $(vLoad);

[jheasley]

So i like the solution that was presented.  I ended up taking the route of having the users utilize self service within a regular application.  they built their own tabular views and then could make selections and do reductions prior to exporting.  a few more steps but it kept people out of the load scripts, which they are more likely to break without good training!

but Massimo's solution is really cool and I'm looking forward to trying it.