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.
michael_gardner
Contributor III

Creating one record per Month

Hey everyone,

I followed the approach defined by hic in his "Generating Missing Data" resource.

Image 11.png

In the example above it is filling in the missing records per day.  However, I need to fill in the missing records per month not per day.


For example, FromDate = 1/1/2013 and ToDate = 1/1/2014 so I need 12 records one for each month (1/1/2013, 2/1/2013, 3/1/2013, etc).

I have used the below solution which seems to work but is not very straightforward.  Is there a more straightforward solution?

Image 12.png

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Re: Creating one record per Month

Michael,

It seems clear. I would have coded differently the last WHERE clause, but it is certainly not  so good (it depends on the date you may have)

WHERE addmonths([Start Date], IterNo()-1) <= [End Date]

So I understand that the loop is done until the End Date. And no need to compute the MonthDiff.

Fabrice

3 Replies
Not applicable

Re: Creating one record per Month

Hi Michael, Can you please share the source data ?

michael_gardner
Contributor III

Re: Creating one record per Month

Hey Dathu,


Not sure if I'm allowed to share the data even if it is scrambled, so I would rather not.  However, if you assume the source data is near identical to what Henric is using than it should be close enough. 

Generating Missing Data In QlikView

Again, it appears my solution is working but I tend to make things more complicated than they need to be and run into problems further into the development.

Thanks,
Mike

Not applicable

Re: Creating one record per Month

Michael,

It seems clear. I would have coded differently the last WHERE clause, but it is certainly not  so good (it depends on the date you may have)

WHERE addmonths([Start Date], IterNo()-1) <= [End Date]

So I understand that the loop is done until the End Date. And no need to compute the MonthDiff.

Fabrice

Community Browser