populate missing values

[ilanbaruch]

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

[tamilarasu]

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;

[MarcoWedel]

maybe helpful:

Generating Missing Data In QlikView

regards

Marco

[ilanbaruch]

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?

[tamilarasu]

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: