Announcements
cancel
Showing results for
Did you mean:
Creator III

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)

• Peek ()

1 Solution

Accepted Solutions
Creator III
Author

Hi @Kushal_Chawda , 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.

11 Replies
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:
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
If( IsNull( Value ), Peek( Value ), Value ) as Value
Resident RAW
order by Date ASC;
drop table RAW;``````
Creator III
Author

Hi,

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.

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
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

Creator III
Author

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

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

Creator III
Author

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

Specialist II

yes thats different to the first approach.

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

Creator III
Author

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

``````RAW:
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:
max(Date) as MaxDate;
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)
Resident RAW;

Join(Full)
Resident RAW;

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

DROP Table Full;

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

DROP Table Full_Key;

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

drop table RAW;``````

see the attached

Community Browser