Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Auto fill the missing value

Hi,

I have a table that containts update date and relevant updated value like below:

Record IDIncident DateUpdate DateValue 1Value 2Value 3
107/01/201117/02/2011132163175
107/01/201121/02/2011172823745






I would like to have values between update dates automatically filled like below:

Record IDIncident DateUpdate DateValue 1Value 2Value 3
17/1/201117/02/2011132163175
17/1/201118/02/2011132163175
17/1/201119/02/2011132163175
17/1/201120/02/2011132163175
17/1/201121/02/2011172823745

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

1 Solution

Accepted Solutions
Not applicable
Author

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;

View solution in original post

2 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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;