"unpivot" the date table

[#1]

Hello,

please advise if possible to "unpivot" the table with dates.

(this is possible in Excel with VBA or in Python, but it would be great to have the script in QV or QS).

Sample input is:

(ticket ID, start date, closed date)

ID	startdate	closedate
1	1-May-15	4-May-15
2	3-May-15	5-May-16

Required output is:

ID	ActiveDates
1	1-May-15
1	2-May-15
1	3-May-15
1	4-May-15
2	3-May-15
2	4-May-15
2	5-May-15

Thanks a lot!

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD ID,

  Date(startdate + IterNo() - 1) as ActiveDates

While startdate + IterNo() - 1 <= closedate;

LOAD * INLINE [

       ID, startdate, closedate

    1, 1-May-15, 4-May-15

    2, 3-May-15, 5-May-15

];

[#3]

this is perfect solution.

small comment - at first I did not understand why the algorithm did not work in my QV or QS.

then i realized that you used SET DateFormat='D-MMM-YYYY';

thanks!

[sunny_talwar]

Yes, I apologize for not highlighting that. I should have made a note of that in the script. But I am glad you were quick to pick that up