topic Re: Exists Condition in QlikView

Exists Condition

Tue, 26 Aug 2014 07:11:09 GMT

Hi all,

I have confusion while bringing the Exists Condition in my script.

My script contains both Sales and Stock Data.

Stock contains 6 records and in Sales 5 records.

So I have to create a dummy records(6th record) in Sales and should consider weight as zero for that dummy record.

I created the script, but somewhere wrong in Exists Condition.

So Kindly help me to solve this issue. Please find the attachment.

Regards,

Sivasu

Re: Exists Condition

jonathandienst — Tue, 26 Aug 2014 07:36:41 GMT

My understanding is that you want to populate a dummy record for a key combination that exists in the stock table, but not in the sales table. To do this, add a SalesKey (which exists only in the Sales table) and use this for the Exists. The final load should be from the Stock source or the STOCK resident table as this data does not exist in the Sales source:

SALES:

LOAD

CODE as SalesCode,

CATEGORY as SalesCategory,

COMPANY as SalesCompany,

WEIGHT as SalesWeight,

COMPANY&'-'&CATEGORY&'-'&CODE as Key,

COMPANY&'-'&CATEGORY&'-'&CODE as SalesKey

FROM

(ooxml, embedded labels, table is SALES);

Concatenate(SALES)

LOAD Distinct

StockCode As SalesCode,

StockCategory As SalesCategory,

StockCompany as SalesCompany,

0 as SalesWeight,

StockCompany&'-'&StockCategory&'-'&StockCode as Key

Resident STOCK

Where Not Exists(SalesKey, StockCompany&'-'&StockCategory&'-'&StockCode);

DROP Field SalesKey; // not needed any more

HTH

Jonathan

Re: Exists Condition

Tue, 26 Aug 2014 08:02:39 GMT

Thanks Jonathan.