Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table that containts update date and relevant updated value like below:
Record ID | Incident Date | Update Date | Value 1 | Value 2 | Value 3 |
1 | 07/01/2011 | 17/02/2011 | 13 | 216 | 3175 |
1 | 07/01/2011 | 21/02/2011 | 17 | 282 | 3745 |
I would like to have values between update dates automatically filled like below:
Record ID | Incident Date | Update Date | Value 1 | Value 2 | Value 3 |
1 | 7/1/2011 | 17/02/2011 | 13 | 216 | 3175 |
1 | 7/1/2011 | 18/02/2011 | 13 | 216 | 3175 |
1 | 7/1/2011 | 19/02/2011 | 13 | 216 | 3175 |
1 | 7/1/2011 | 20/02/2011 | 13 | 216 | 3175 |
1 | 7/1/2011 | 21/02/2011 | 17 | 282 | 3745 |
so that I can create a trend line chart with values for "missing dates". Can someone help me with this? Thanks in advance.
Regards,
Yang
Hi Gysbert,
Thanks for the help. The blog post helps.
My scenario is slightly more complicate (additional columsn apart from minimum "date" and "value" fields). So I need to build an iteration to run through the "join and peek" logic for multiple times. I couldn't find a way to upload QVW in reply so I pasted my load scripts below. Hope someone will find this is useful.
Regards,
Yang
---------------------------
IncidentTemp:
LOAD [Incident ID],
[Incident Date],
[Update Date],
[Value 1],
[Value 2],
[Value 3]
FROM
[Changing data.xlsx]
(ooxml, embedded labels, table is Sheet1);
UniqueIncident:
Load distinct Num#([Incident ID]) as [IID], [Incident Date] as [IDate]
Resident IncidentTemp
order by [Incident ID];
Let vNoOfRows = NoOfRows('UniqueIncident');
FOR i=0 to $(vNoOfRows)-1
set vIncidentID=Peek('IID',$(i),'UniqueIncident');
set vIncidentDate=Peek('IDate',$(i),'UniqueIncident');
MinMaxDate:
Load Min([Update Date]) as MinDate, Max([Update Date]) as MaxDate ;
load *
resident IncidentTemp
where [Incident ID]=$(vIncidentID);
Let vMinDate = Peek('MinDate',-1,'MinMaxDate') ;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;
Drop Table MinMaxDate;
Join (IncidentTemp)
Load
$(vIncidentID) as [Incident ID],
Date(recno()+$(vMinDate)) as [Update Date]
Autogenerate vMaxDate - vMinDate;
NEXT i;
Incident:
NoConcatenate Load
[Incident ID],
If( IsNull( [Incident Date] ), Peek( [Incident Date] ), [Incident Date] ) as [Incident Date],
[Update Date],
If( IsNull( [Value 1] ), Peek( [Value 1] ), [Value 1] ) as [Value 1],
If( IsNull( [Value 2] ), Peek( [Value 2] ), [Value 2] ) as [Value 2],
If( IsNull( [Value 3] ), Peek( [Value 3] ), [Value 3] ) as [Value 3]
Resident IncidentTemp
Order By [Incident ID],[Update Date] ;
Drop Table IncidentTemp;
Drop Table UniqueIncident;
See this blog post: How to populate a sparsely populated field
Hi Gysbert,
Thanks for the help. The blog post helps.
My scenario is slightly more complicate (additional columsn apart from minimum "date" and "value" fields). So I need to build an iteration to run through the "join and peek" logic for multiple times. I couldn't find a way to upload QVW in reply so I pasted my load scripts below. Hope someone will find this is useful.
Regards,
Yang
---------------------------
IncidentTemp:
LOAD [Incident ID],
[Incident Date],
[Update Date],
[Value 1],
[Value 2],
[Value 3]
FROM
[Changing data.xlsx]
(ooxml, embedded labels, table is Sheet1);
UniqueIncident:
Load distinct Num#([Incident ID]) as [IID], [Incident Date] as [IDate]
Resident IncidentTemp
order by [Incident ID];
Let vNoOfRows = NoOfRows('UniqueIncident');
FOR i=0 to $(vNoOfRows)-1
set vIncidentID=Peek('IID',$(i),'UniqueIncident');
set vIncidentDate=Peek('IDate',$(i),'UniqueIncident');
MinMaxDate:
Load Min([Update Date]) as MinDate, Max([Update Date]) as MaxDate ;
load *
resident IncidentTemp
where [Incident ID]=$(vIncidentID);
Let vMinDate = Peek('MinDate',-1,'MinMaxDate') ;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;
Drop Table MinMaxDate;
Join (IncidentTemp)
Load
$(vIncidentID) as [Incident ID],
Date(recno()+$(vMinDate)) as [Update Date]
Autogenerate vMaxDate - vMinDate;
NEXT i;
Incident:
NoConcatenate Load
[Incident ID],
If( IsNull( [Incident Date] ), Peek( [Incident Date] ), [Incident Date] ) as [Incident Date],
[Update Date],
If( IsNull( [Value 1] ), Peek( [Value 1] ), [Value 1] ) as [Value 1],
If( IsNull( [Value 2] ), Peek( [Value 2] ), [Value 2] ) as [Value 2],
If( IsNull( [Value 3] ), Peek( [Value 3] ), [Value 3] ) as [Value 3]
Resident IncidentTemp
Order By [Incident ID],[Update Date] ;
Drop Table IncidentTemp;
Drop Table UniqueIncident;