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: 
nenadvukovic
Creator III
Creator III

Best way to create additional rows based on the from-to periods

Hi, is it possible to write a script to read the Source table and create a Result table in order to be able to show the trend line by periods?

Thank you

Source:

load * inline [

FromYY-MM, ToYY-MM, Amount

16-09, 16-12, 1

16-11, 17-02, 2];

Result:

load * inline [

FromYY-MM, ToYY-MM, Amount

16-09, 16-09, 1

16-10, 16-10, 1

16-11, 16-11, 1

16-12, 16-12, 1

16-11, 16-11, 2

16-12, 16-12, 2

17-01, 17-01, 2

17-02, 17-02, 2];

drop table Source;

1 Solution

Accepted Solutions
sunny_talwar

Although I am not sure I understand the point of having both FromYYMM and ToYYMM when they are the same in the result. May be create YYMM instead of those two fields.

Capture.PNG

Script:

Source:

LOAD Date(AddMonths([FromYYMM], IterNo() - 1), 'YY-MM') as [YYMM],

  Amount

While AddMonths([FromYYMM], IterNo() - 1) <= [ToYYMM];

LOAD Date(MonthStart(Date#(FromYYMM, 'YY-MM')), 'YY-MM') as [FromYYMM],

  Date(MonthStart(Date#(ToYYMM, 'YY-MM')), 'YY-MM') as [ToYYMM],

  Amount

inline [

FromYYMM, ToYYMM, Amount

16-09, 16-12, 1

16-11, 17-02, 2];

View solution in original post

3 Replies
sunny_talwar

Try this:

Source:

LOAD Date(AddMonths([FromYYMM], IterNo() - 1), 'YY-MM') as [FromYYMM],

  Date(AddMonths([FromYYMM], IterNo() - 1), 'YY-MM') as [ToYYMM],

  Amount

While AddMonths([FromYYMM], IterNo() - 1) <= [ToYYMM];

LOAD Date(MonthStart(Date#(FromYYMM, 'YY-MM')), 'YY-MM') as [FromYYMM],

  Date(MonthStart(Date#(ToYYMM, 'YY-MM')), 'YY-MM') as [ToYYMM],

  Amount

inline [

FromYYMM, ToYYMM, Amount

16-09, 16-12, 1

16-11, 17-02, 2];


Capture.PNG

sunny_talwar

Although I am not sure I understand the point of having both FromYYMM and ToYYMM when they are the same in the result. May be create YYMM instead of those two fields.

Capture.PNG

Script:

Source:

LOAD Date(AddMonths([FromYYMM], IterNo() - 1), 'YY-MM') as [YYMM],

  Amount

While AddMonths([FromYYMM], IterNo() - 1) <= [ToYYMM];

LOAD Date(MonthStart(Date#(FromYYMM, 'YY-MM')), 'YY-MM') as [FromYYMM],

  Date(MonthStart(Date#(ToYYMM, 'YY-MM')), 'YY-MM') as [ToYYMM],

  Amount

inline [

FromYYMM, ToYYMM, Amount

16-09, 16-12, 1

16-11, 17-02, 2];

nenadvukovic
Creator III
Creator III
Author

Hi Sunny, that is great and so fast.

Yes, you are absolutely right about the redundancy of both From and To fields in the resulting table. When preparing the case data I didn't think it through.

Thank you