Fill missing dates and use old value

[qlikwiz123]

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

[zhadrakas]

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;

[qlikwiz123]

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.

[zhadrakas]

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

[qlikwiz123]

Hi @zhadrakas 

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

[zhadrakas]

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

[qlikwiz123]

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

[zhadrakas]

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 

[qlikwiz123]

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:

Desired Output:

Date	ID	Type	Value
7/23/20202	15	Cups	200
7/24/2020	15	Cups	150
7/25/2020	15	Cups	150
7/26/2020	15	Cups	150
7/27/2020	15	Cups	500

Similarly, for other IDs and Type

[Kushal_Chawda]

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