topic Re: Populating missing values in QlikView

Populating missing values

WalidChoukri — Wed, 17 Mar 2021 16:10:52 GMT

Hello Everyone,

I'm trying to Populate missing values of the dates in between with peek function, i used the following code but still i couldn't get the results i'm looking for :

Table: LOAD * INLINE [ Date, Article, Quantity 01/02/2021, A, 10 03/02/2021, A, 20 06/02/2021, B, 30 07/02/2021, C, 40 ]; Join(Table) LOAD Date(MinDate+IterNO ()-1) as Date While(MinDate+IterNO ()-1) <= Num(MaxDate -1); LOAD Min(Date) as MinDate, Max(Date) As MaxDate Resident Table; Join(Table) Load If( Len(Quantity) = 0, peek('newQuantity'),Quantity) as newQuantity Resident Table;

I attached my file, any help is much appreciated. Thank you !!

Re: Populating missing values

Dalton_Ruer — Thu, 22 Apr 2021 14:54:52 GMT

1. Be sure to sort the data in your final load in date order

Re: Populating missing values

MayilVahanan — Fri, 23 Apr 2021 08:48:38 GMT

Hi @WalidChoukri

Try like attached file

script:

Table:

LOAD * INLINE [
Date, Article, Quantity
01/02/2021, A, 10
03/02/2021, A, 20
06/02/2021, B, 30
07/02/2021, C, 40
];

DateT:
LOAD
Date(MinDate+IterNO ()-1) as Date
While(MinDate+IterNO ()-1) <= Num(MaxDate);
LOAD
Min(Date) as MinDate,
Max(Date) As MaxDate
Resident Table;

T1:
Load Date as StartDate, Article, Quantity, Date(Alt(Peek(StartDate)-1, Today())) as EndDate Resident Table Order by Date desc;

DROP Table Table;

IntervalMatch(Date)
LOAD StartDate, EndDate Resident T1;

Join
LOAD * Resident T1;

DROP Table T1, DateT;

Re: Populating missing values

WalidChoukri — Tue, 27 Apr 2021 11:17:20 GMT

Thank you so much for your help

Re: Populating missing values

MayilVahanan — Tue, 27 Apr 2021 11:29:23 GMT

Welcome. Happy to help you