Set fieldvalue to 0 if field does not appear in reference list

[danieloberbilli]

Dear All,

same problem as before in my previous post Only consider fields from reference list in script run

that was answered correctly by swuehl, now I am facing the problem that I need to have the full list of products...so I need to set their value to 0  - maybe by multiplying the fields with zero.

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 DataNow the problem:

4) Intended result: QlikView should set the Product fieldvalues to 0 if they shoudnt be considered due to 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.

Any ideas?

 

[#2 · Accepted solution]

Hi Daniel,

I would suggest two functions, CrossTable and Apply Map.

Reference_Products_to_Include:

Mapping load  * INLINE [

    Product,Qty

    Product_B,1

    Product_D,1

];

DataTMP:

CrossTable(Product, Data)

LOAD * FROM

(qvd);

NoConcatenate

Final:

LOAD 

     ApplyMap('Reference_Products_to_Include',Product,0) as Data,

     OrderID,

  Product

resident DataTMP;

drop table DataTMP;