Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.
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];
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];
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.
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];
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