Adding all 12 months

[#1]

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

[Gysbert_Wassenaar]

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

and this document: Generating Missing Data In QlikView

[sunny_talwar]

Try this:

Table:

LOAD UserID,

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

While IterNo() <= 12;

LOAD * Inline [

UserID

100001

100002

];

[#4]

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

[Gysbert_Wassenaar]

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

[sunny_talwar · Accepted solution]

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;

[#7]

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