Only consider fields from reference list in script run

[danieloberbilli]

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

[swuehl · Accepted solution]

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

];

[danieloberbilli]

Great! Thanks a lot!!