topic Re: Adding all 12 months in QlikView

Adding all 12 months

Wed, 25 Nov 2020 16:16:04 GMT

Hi Friends,

I have a data which has only user id's. I need to add all the 12 months to those userid

example:

My Data:

UserID

100001

100002

Expected Result

UserID MonthEndDate

100001 01/31/2015

100001 02/29/2015

100001 03/31/2015

100002 01/31/2015

100002 02/29/2015

100002 03/31/2015

Is there any way to achieve this in our Qlikview.

Thanks in advance for the solutions....

Regards,

Anand B Nagaraj

Re: Adding all 12 months

Gysbert_Wassenaar — Mon, 16 May 2016 10:21:14 GMT

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

and this document: Generating Missing Data In QlikView

Re: Adding all 12 months

sunny_talwar — Mon, 16 May 2016 10:25:01 GMT

Try this:

Table:

LOAD UserID,

Date(MonthEnd(MakeDate(2015,1,1), IterNo() - 1)) as MonthEndDate

While IterNo() <= 12;

LOAD * Inline [

UserID

100001

100002

];

Re: Adding all 12 months

Tue, 17 May 2016 05:45:02 GMT

Hi,

Thanks for the reply.

I need to have this for not a particular year

example

UserID	Date
10001	1/16/2016
10002	3/28/2016
10003	5/17/2016
10001	3/16/2016
10002	2/28/2016
10003	1/2/2013

If you check, the minimum year is 2013 & the max year is 2016, so now, i need to generate the month end date for all the users starting from 1stJan13 till 2ndFeb16.

Is there any way to work around on the same.

Thanks for the help in advance.

Regards,

Anand B Nagaraj

Re: Adding all 12 months

Gysbert_Wassenaar — Tue, 17 May 2016 15:18:30 GMT

Then change the script Sunny posted. How hard is it to change 2015 to 2013 and 12 to 38?

Re: Adding all 12 months

sunny_talwar — Tue, 17 May 2016 15:23:15 GMT

Or may be this:

Table:

LOAD * Inline [

UserID, Date

10001, 1/16/2016

10002, 3/28/2016

10003, 5/17/2016

10001, 3/16/2016

10002, 2/28/2016

10003, 1/2/2013

];

Join (Table)

LOAD Floor(MonthEnd(Min(Date))) as MinDate

Resident Table;

FinalTable:

LOAD UserID,

Date,

Date(AddMonths(MinDate, IterNo() - 1)) as MonthEndDate

Resident Table

While AddMonths(MinDate, IterNo() - 1) <= Date;

DROP Table Table;

Re: Adding all 12 months

Thu, 19 May 2016 06:28:22 GMT

Thanks Sunny, your answer made me helpful by changing according to my requirement