Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Fill missing dates and use old value

Hi,

I have dates and values associated as shown below

Date                   Value

07/23/2020 – 1000

07/24/2020 – 1200

07/27/2020 – 800

 

Is there a way to fill the missing dates and use the same value prior to that? 

For example, generate missing dates 7/25, 7/26 and show the previous value from 7/24, which is 1200

Output:

 

Date                   Value

07/23/2020 – 1000

07/24/2020 – 1200

07/25/2020 – 1200

7/26/2020    - 1200

7/27/2020   - 800

Labels (3)
1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

Hi @Kush , thank you so much.

With a little change in the expression 

from

     If( IsNull(Value),Peek(Value),Value) as Value

to

If(ID = Peek(ID) and Type = Peek(Type) and IsNull(Value), Peek(Value), Value) as Value

I got this working with my actual data. Thanks @sunny_talwar for the expression. Also, @zhadrakas  thank you for all the help. Every bit helped and is highly appreciated.

 

View solution in original post

11 Replies
Highlighted
Specialist II
Specialist II

this should do it

RAW:
Load 1 as Dim, MakeDate(2020, 7, 23) as Date, 1000 as Value AutoGenerate(1);
Load 1 as Dim, MakeDate(2020, 7, 29) as Date, 1200 as Value AutoGenerate(1);
Load 1 as Dim, MakeDate(2020, 8, 27) as Date, 800 as Value AutoGenerate(1);

//Get min & Max Date
temp:
LOAD min(Date) as MinDate,
     max(Date) as MaxDate
RESIDENT RAW;

LET varMinDate = Num(Peek('MinDate',-1,'temp'));
LET varMaxDate = Num(Peek('MaxDate',-1,'temp'));
drop table temp;

//Fill Date Gaps
join (RAW)
LOAD date($(varMinDate)+ rowno() -1) AS Date
AUTOGENERATE $(varMaxDate)-$(varMinDate)+1;

//fill gaps with previous values
FINAL:
NoConcatenate
Load Date,
     If( IsNull( Value ), Peek( Value ), Value ) as Value
Resident RAW
order by Date ASC;
drop table RAW;
Highlighted
Creator II
Creator II

Hi,

@zhadrakas 

In my sample QVW, it is failing. Can you please tell me what's wrong here? I also have ID and Type as additional fields and maybe that's breaking your logic.

I may also add more fields in the future, so don't want this logic to break if I add new fields.

Highlighted
Specialist II
Specialist II

change the last part of the script like this.

maybe you want to add ID or Type in order by statement if you want to change the logic.

FINAL:
NoConcatenate
Load Date,
     If( IsNull( Value ), Peek( Value ), Value ) as Value,
     If( IsNull( ID ), Peek( ID ), ID ) as ID,
     If( IsNull( Type ), Peek( Type ), Type ) as Type
Resident RAW
order by Date ASC;
drop table RAW;

regards

Highlighted
Creator II
Creator II

Hi @zhadrakas 

 

Still does not work. Please look at the QVW attached.

Highlighted
Specialist II
Specialist II

please explain in detail what is not working.

i've downloaded your qvw and everything works as expected. there is no gap in the data model.

regards

Highlighted
Creator II
Creator II

qlikwiz123_0-1595947663413.png

 

qlikwiz123_1-1595947695217.png

 

If you look at these instances, the dates aren't populated for every ID for every Type.

 

Highlighted
Specialist II
Specialist II

yes thats different to the first approach.

can you please show me a sample with your desired output?

the problem with that is that if i change the logic to fill up all "Date-ID-Type-combinations, it will add more datasets than just the gaps of the dates that are not existing.

if there are more columns in future that are important for you, please add them right now. 

regards 

Highlighted
Creator II
Creator II

Hi,

For every ID-Type-Date-Value row, I want all the dates to be generated and for the missing Value, it should pick up the last Value and repeat it until a new date with value is present.

Present:

qlikwiz123_1-1596031130122.png

Desired Output:

DateIDTypeValue
7/23/2020215Cups200
7/24/202015Cups150
7/25/202015Cups150
7/26/202015Cups150
7/27/202015Cups500

 

Similarly, for other IDs and Type

Highlighted
MVP
MVP

RAW:
LOAD *, AutoNumberHash256(Date&ID&Type) as Key;
LOAD * INLINE [
Date,ID,Type,Value
07/23/2020,11,Plates,1000
07/24/2020,11,Plates,1200
7/27/2020,11,Plates,1000
07/23/2020,11,Cups,800
07/24/2020,11,Cups,1200
07/27/2020,11,Cups,500
07/23/2020,15,Plates,850
07/24/2020,15,Plates,500,
07/25/2020,15,Plates,700
7/27/2020,15,Plates,1000
07/23/2020,15,Cups,200
07/24/2020,15,Cups,150
07/27/2020,15,Cups,500
];

//Get min & Max Date
temp:
LOAD min(Date) as MinDate,
     max(Date) as MaxDate;
LOAD FieldValue('Date',RecNo()) as Date
AutoGenerate FieldValueCount('Date');

LET varMinDate = Num(Peek('MinDate',-1,'temp'));
LET varMaxDate = Num(Peek('MaxDate',-1,'temp'));

drop table temp;

//Fill Date Gaps
NoConcatenate
Full:
LOAD date($(varMinDate)+ rowno() -1) AS Date
AUTOGENERATE $(varMaxDate)-$(varMinDate)+1;

Join(Full)
LOAD Distinct ID
Resident RAW;

Join(Full)
LOAD Distinct Type
Resident RAW;

Full_Key:
LOAD *,
    AutoNumberHash256(Date&ID&Type) as Key1
Resident Full;

DROP Table Full;

Concatenate(RAW)
LOAD *
Resident Full_Key
where not Exists(Key,Key1);

DROP Table Full_Key;

//fill gaps with previous values
FINAL:
NoConcatenate
Load Date,
      ID,
      Type,
     If( IsNull(Value),Peek(Value),Value) as Value
Resident RAW
order by ID,Type,Date;

drop table RAW;

 

see the attached