topic Re: Only consider fields from reference list in script run in QlikView

Only consider fields from reference list in script run

danieloberbilli — Sun, 04 May 2014 22:44:56 GMT

Dear All,

Please have a look at the following issue:

1) I have a dataset with a range of about 300 Product-fields: e.g. OrderID, Product_A, Product_B, Product_C, Product_D.....

2) Before the script run, the user saves an Excel file with a reference list of which product fields should be included in the script run

3) The script loads the excel file (in attached example as an inline load) and the Data

Now the problem:

4) Intended result: QlikView should only consider fields mentioned in the reference list. The reference list could be either a field list (multiple columns but with no value) or a list of values (one column and multiple rows) as it comes from a field export function from a previous QlikView app.

Please note, that it must be this way/sequence as the script runs a complex statistical calculation.

I think, I have to set variables for each product to check whether the product is found in the reference list or not and then let the variable be either 0 or 1 - to multiply with the field, but I didnt manage to do so.

See qvw example attached and screenshot below:

Please note: its not about making selections or set analysis on the dashboard! Its all about the script run.

Thanks a lot for your help.

All the best

Daniel

Re: Only consider fields from reference list in script run

swuehl — Sun, 04 May 2014 22:59:07 GMT

Try maybe something like this, creating the field list for the LOAD using a variable expansion:

Reference_Products_to_Include:

LOAD * INLINE [

ProductsInclude

Product_B

Product_D

];

TMP:

LOAD concat(DISTINCT ProductsInclude,',') as TmpProducts Resident Reference_Products_to_Include;

Let vProducts = peek('TmpProducts',0);

Data:

LOAD OrderID, $(vProducts);

LOAD * INLINE [

OrderID, Product_A, Product_B, Product_C, Product_D

1, 1, 0, 0, 1

2, 1, 1, 0, 0

3, 0, 0, 1, 1

4, 1, 1, 1, 1

5, 0, 1, 0, 0

];

Re: Only consider fields from reference list in script run

danieloberbilli — Sun, 04 May 2014 23:06:18 GMT

Great! Thanks a lot!!