Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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?

2 Replies
Not applicable

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

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

Hi Rodrigo,

one solution could be also:

QlikCommunity_Thread_120203_Pic1.JPG.jpg

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

Community Browser