Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Adding all 12 months

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

6 Replies
Gysbert_Wassenaar

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

and this document: Generating Missing Data In QlikView


talk is cheap, supply exceeds demand
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

];


Capture.PNG

Not applicable
Author

Hi,

Thanks for the reply.

I need to have this for not a particular year

example

  

UserIDDate
100011/16/2016
100023/28/2016
100035/17/2016
100013/16/2016
100022/28/2016
100031/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?


talk is cheap, supply exceeds demand
sunny_talwar

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;

Not applicable
Author

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