Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table as below in the QVW attached.
RAW:
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
];
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.
In my sample QVW, it is failing. Not sure 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.
@sunny_talwar I had seen you solve something similar in the past. Any help is highly appreciated 🙂
Try this
RAW:
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
RESIDENT RAW;
LET varMinDate = Num(Peek('MinDate',-1,'temp'));
LET varMaxDate = Num(Peek('MaxDate',-1,'temp'));
drop table temp;
//Fill Date Gaps
tmp:
LOAD date($(varMinDate)+ rowno() -1) AS Date
AUTOGENERATE $(varMaxDate)-$(varMinDate)+1;
Join (tmp)
LOAD ID,
Type
Resident RAW;
Join (RAW)
LOAD DISTINCT *
Resident tmp;
DROP Table tmp;
//fill gaps with previous values
FINAL:
NoConcatenate
LOAD Date,
ID,
Type,
If(ID = Peek(ID) and Type = Peek(Type) and IsNull(Value), Peek(Value), Value) as Value
Resident RAW
Order By ID, Type, Date ASC;
DROP Table RAW;
please do not duplicate the same Ticket.
here are already information about his problem.
Try this
RAW:
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
RESIDENT RAW;
LET varMinDate = Num(Peek('MinDate',-1,'temp'));
LET varMaxDate = Num(Peek('MaxDate',-1,'temp'));
drop table temp;
//Fill Date Gaps
tmp:
LOAD date($(varMinDate)+ rowno() -1) AS Date
AUTOGENERATE $(varMaxDate)-$(varMinDate)+1;
Join (tmp)
LOAD ID,
Type
Resident RAW;
Join (RAW)
LOAD DISTINCT *
Resident tmp;
DROP Table tmp;
//fill gaps with previous values
FINAL:
NoConcatenate
LOAD Date,
ID,
Type,
If(ID = Peek(ID) and Type = Peek(Type) and IsNull(Value), Peek(Value), Value) as Value
Resident RAW
Order By ID, Type, Date ASC;
DROP Table RAW;