Auto fill the missing value

Mon, 17 Jun 2013 04:38:02 GMT

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

Re: Auto fill the missing value

Gysbert_Wassenaar — Mon, 17 Jun 2013 08:47:51 GMT

See this blog post: How to populate a sparsely populated field

Re: Auto fill the missing value

Tue, 18 Jun 2013 12:45:09 GMT

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;

topic Re: Auto fill the missing value in QlikView

Auto fill the missing value

Re: Auto fill the missing value

Re: Auto fill the missing value