topic populate missing values in QlikView

populate missing values

ilanbaruch — Mon, 26 Feb 2018 17:40:47 GMT

hi all,

in my load script i have a table as below

date	product_id	In_Stock	Wearhouse
15/01/17	123	yes	WH1
16/01/17	123	yes	WH1
17/01/17	123	yes	WH1
18/01/17	123	yes	WH1
19/01/17	123	yes	- (Null)
20/01/17	123	yes	- (Null)
21/01/17	123	yes	- (Null)
22/01/17	123	yes	- (Null)

if Product_id is the same Product_id and In_Stock value is the same i want to populate the Null's with 'WH1'

any ideas/?

advanced thanks

Re: populate missing values

tamilarasu — Mon, 26 Feb 2018 17:51:36 GMT

Hi ilan

Data:
LOAD * INLINE [
    date, product_id, In_Stock, Warehouse
    15/01/17, 123, yes, WH1
    16/01/17, 123, yes, WH1
    17/01/17, 123, yes, WH1
    18/01/17, 123, yes, WH1
    19/01/17, 123, yes
    20/01/17, 123, yes
    21/01/17, 123, yes
    22/01/17, 123, yes
];

NoConcatenate
Result:
Load date,
product_id,
In_Stock,
If(product_id = peek(product_id) and In_Stock= peek(In_Stock) and Len(Trim(Warehouse))=0, Peek(Warehouse), Warehouse) as Warehouse
Resident Data Order by date, product_id, In_Stock;

DROP Table Data;

Re: populate missing values

MarcoWedel — Mon, 26 Feb 2018 22:28:50 GMT

maybe helpful:

Generating Missing Data In QlikView

regards

Marco

Re: populate missing values

ilanbaruch — Thu, 01 Mar 2018 08:28:48 GMT

thank you Tamil,

but my table has only the last dates values

i.e

from 15/01/17 to 18/01/17 is null

from 19/01/17 to 22/01/17 is populated

i need to populate retro dates

i tried your example with a order by date desc; but it wouldn't work, any ideas?

Re: populate missing values

tamilarasu — Fri, 02 Mar 2018 05:36:16 GMT

Hi ilan,

The below code is working find in both the cases. Kindly check and let us know.

Data:  
LOAD * INLINE [  
    date, product_id, In_Stock, Warehouse  
    15/01/17, 123, yes
    16/01/17, 123, yes 
    17/01/17, 123, yes, WH1  
    18/01/17, 123, yes, WH1  
    19/01/17, 123, yes, WH1  
    20/01/17, 123, yes  
    21/01/17, 123, yes  
    22/01/17, 123, yes  
];  
  
NoConcatenate  
Result_Temp:  
Load date,  
product_id,  
In_Stock,  
If(product_id = peek(product_id) and In_Stock= peek(In_Stock) and Len(Trim(Warehouse))=0, Peek(Warehouse), Warehouse) as Warehouse  
Resident Data Order by date, product_id, In_Stock; 

NoConcatenate
Final_Result: 
Load date,  
product_id,  
In_Stock,  
If(product_id = peek(product_id) and In_Stock= peek(In_Stock) and Len(Trim(Warehouse))=0, Peek(Warehouse), Warehouse) as Warehouse  
Resident Result_Temp Order by date desc, product_id desc;  
  
DROP Table Data, Result_Temp;

Output: