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

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

danieloberbilli — Mon, 05 May 2014 01:59:43 GMT

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?

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

Mon, 05 May 2014 03:44:32 GMT

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;