topic Creating one record per Month in QlikView

Creating one record per Month

Anonymous — Thu, 30 Jan 2014 21:02:43 GMT

Hey everyone,

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

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?

Re: Creating one record per Month

Thu, 30 Jan 2014 21:12:15 GMT

Hi Michael, Can you please share the source data ?

Re: Creating one record per Month

Anonymous — Thu, 30 Jan 2014 22:18:26 GMT

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

Re: Creating one record per Month

Thu, 30 Jan 2014 22:38:21 GMT

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