topic Re: Controlling Fields Using an External File in App Development

Controlling Fields Using an External File

m_perreault — Mon, 07 May 2018 17:15:24 GMT

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

Re: Controlling Fields Using an External File

jheasley — Mon, 07 May 2018 18:21:56 GMT

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.

Re: Controlling Fields Using an External File

m_perreault — Mon, 07 May 2018 21:03:58 GMT

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.

Re: Controlling Fields Using an External File

maxgro — Mon, 07 May 2018 21:29:31 GMT

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);

Re: Controlling Fields Using an External File

m_perreault — Mon, 07 May 2018 21:56:54 GMT

Work's great, very clever!

Re: Controlling Fields Using an External File

m_perreault — Mon, 07 May 2018 22:09:16 GMT

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?

Re: Controlling Fields Using an External File

m_perreault — Mon, 07 May 2018 22:11:26 GMT

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);

Re: Controlling Fields Using an External File

jheasley — Tue, 08 May 2018 00:48:18 GMT

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.