topic Re: Load/Create Missing Values in QlikView

Load/Create Missing Values

Tue, 11 Jun 2013 19:53:31 GMT

Hi All,

I have two resident tables

[SLAES] and [PRODUCT]

The product table is missing few items. I would like to add some dummy records and call them unknown as shown in new [PRODDUCT] table

whatI tried to do was

left join(PRODUCT)

load PRODUCT_ID, 'UNKNOWN' as NAME resident [Sales] where /* Need help here */ NOT exists (PROD_ID) in [PRODUCT] Table

How do I do this?

Thanks,

Aji Paul.

Re: Load/Create Missing Values

Tue, 11 Jun 2013 20:34:50 GMT

Here is a workaround I have , but very complex unnecessarily .

Experts would you please review and suggest and alternative?

[Sales]:
load * inline
[ITM_ID,PROD_ID,QTY,AMT
12,P12,1,20
13,P15,2,70
14,P98,3,30];

[PRODUCT_TEMP]:
load * inline
[PROD_ID,NAME
P12,Book
P15,DVD
p75,CD];

[PRODUCT_TEMP2]:
NoConcatenate Load DISTINCT PROD_ID
Resident Sales;
join(PRODUCT_TEMP2)
load PROD_ID, NAME
resident PRODUCT_TEMP;

drop table PRODUCT_TEMP;

[Product]:
NoConcatenate load PROD_ID, if(isnull(NAME), 'UNKNOWN', NAME) as NAME
Resident PRODUCT_TEMP2;

drop table PRODUCT_TEMP2;

Re: Load/Create Missing Values

hic — Tue, 11 Jun 2013 21:16:27 GMT

Sales:

Load PROD_ID, ... from Sales;

Products:

Load PROD_ID, PROD_ID as PROD_ID2, NAME from Product;

Concatenate (Products)

Load distinct PROD_ID, 'UNKNOWN' as NAME

Resident Sales

Where not Exists(PROD_ID2, PROD_ID);

HIC

Re: Load/Create Missing Values

Tue, 11 Jun 2013 21:39:11 GMT

Thank you HIC