topic "Generating missing data" with Months steps and not Days steps in QlikView

"Generating missing data" with Months steps and not Days steps

Thu, 29 May 2014 12:52:55 GMT

Hi forum,

I have readed and I'm aware of the doc "Generating missing data" I used it and works, now I have to do the same but instead of generating missing data with days steps, I needed to it by months steps.

I have this:

Rates:

LOAD * INLINE [

Date, Rate

01.2014, 2

03.2014, 4

08.2014, 8

];

I need, this output

Date, Rate

01.2014, 2

02.2014, 2

03.2014, 4

04.2014, 4

05.2014, 4

06.2014, 4

07.2014, 4

08.2014, 8

This is code working with days steps, I need to do exactly the same but with months steps, I also leave the example code, just in case.

TempTable_Rates:

Load

Fecha AS Date,

Rate Resident Rates ;

MinMaxDate:

Load

Min(Date) as MinDate,

Max(Date) as MaxDate

resident TempTable_Rates;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;

Drop Table MinMaxDate;

Join (TempTable_Rates)

Load

Date(recno()+$(vMinDate)) as Date

Autogenerate vMaxDate - vMinDate;

Rates:

NoConcatenate

Load

Date(Date,'YYYY-MM-DD'),

If( IsNull( Rate ), Peek( Rate ), Rate ) as Rate

Resident TempTable_Rates

Order By Date ; // so that above values can be propagated downwards

Drop Table TempTable_Rates;

Drop Table Rates;

Can some1 help me?

Re: "Generating missing data" with Months steps and not Days steps

Thu, 29 May 2014 14:28:25 GMT

Rodrigo,

look at example.

If you like, there is only 1 step to go back to your month_year format and join tables after intervalmatch ....

regards

Darek

Re: "Generating missing data" with Months steps and not Days steps

MarcoWedel — Thu, 29 May 2014 23:35:17 GMT

Hi Rodrigo,

one solution could be also:

Rates:
LOAD Date#(Date, 'MM.YYYY') as Date,
     Rate
INLINE [
    Date, Rate
    01.2014, 2
    03.2014, 4
    08.2014, 8
];

LOAD Date(AddMonths(Date, IterNo()), 'MM.YYYY') as Date,
     Rate
While AddMonths(Date, IterNo())<NextDate;
LOAD *,
     Previous(Date) as NextDate
Resident Rates
Order By Date desc;

hope this helps

regards

Marco