Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Exists Condition

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Exists Condition

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
2 Replies
MVP
MVP

Re: Exists Condition

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Exists Condition

Thanks Jonathan.

Community Browser