Exists Condition

[#1]

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

[jonathandienst · Accepted solution]

Hi

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

[#3]

Thanks Jonathan.