Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

nenadvukovic
Contributor 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

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

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];

3 Replies

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

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

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

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
Contributor III

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

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