Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
The end result should look like this:
ProductionDate | Four_Months_rolling |
---|---|
201601 | 2015 Oct";"2015 Nov";"2015 Dec";"2016 Jan |
201602 | 2015 Nov";"2015 Dec";"2016 Jan";"2016 Feb |
201603 | 2015 Dec";"2016 Jan";"2016 Feb";"2016 Mar |
etc | |
I need the code to be in the Data Load Editor so I wrote something like this:
Load *,
Date(Date#(ProductionDate - 3, 'YYYYMM'), 'YYYY MMM') & '";"' & Date(Date#(ProductionDate - 2, 'YYYYMM'), 'YYYY MMM') & '";" ' & Date(Date#(%ProductionDate - 1, 'YYYYMM'), 'YYYY MMM') & '";" ' & Date(Date#(ProductionDate, 'YYYYMM'), 'YYYY MMM') as Four_Months_rolling;
Load * inline
[ProductionDate
201601
201602
201603
201604
201605
201606
201607];
It works partially but for the first row for example, 201601, I see something like ";";";" 2016 Jan. I know is because of the month and -3, -2 etc. but I don't know how I should change the year at the same time, to make it run always correct.
Any ideas how could this work?
Thank you very much!
Anca
May be try this
LOAD *,
Date(AddMonths(Date#(ProductionDate, 'YYYYMM'), -3), 'YYYY MMM') & '";"' &
Date(AddMonths(Date#(ProductionDate, 'YYYYMM'), -2), 'YYYY MMM') & '";"' &
Date(AddMonths(Date#(ProductionDate, 'YYYYMM'), -1), 'YYYY MMM') & '";"' &
Date(Date#(ProductionDate, 'YYYYMM'), 'YYYY MMM') as Four_Months_rolling;
LOAD * INLINE [
ProductionDate
201601
201602
201603
201604
201605
201606
201607
];
May be try this
LOAD *,
Date(AddMonths(Date#(ProductionDate, 'YYYYMM'), -3), 'YYYY MMM') & '";"' &
Date(AddMonths(Date#(ProductionDate, 'YYYYMM'), -2), 'YYYY MMM') & '";"' &
Date(AddMonths(Date#(ProductionDate, 'YYYYMM'), -1), 'YYYY MMM') & '";"' &
Date(Date#(ProductionDate, 'YYYYMM'), 'YYYY MMM') as Four_Months_rolling;
LOAD * INLINE [
ProductionDate
201601
201602
201603
201604
201605
201606
201607
];
Thank you very much! I've found the same solution 10 min ago and it works indeed.