Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I need to able to create measures based on the first 3 columns, i need to create 3 measures (AMOUNT_NEXTDATE2, AMOUNT_NEXTDATE3, AMOUNT_NEXTDATE4)
the AMOUNT_NEXTDATE2/3/4's value should be based on the next month
e.g. DATE_2 20160201 AMOUNT 10, value for AMOUNT_NEXT DATE2 should be 20 because the value for 20160301 is 20 which is the next month.
i tried doing a self-join but it doesn't work well for me. Any help? thanks!
DATE_1 | DATE_2 | AMOUNT | AMOUNT_NEXT DATE2 | AMOUNT_NEXT DATE3 | AMOUNT_NEXT DATE4 |
20170801 | 20160201 | 10 | 20 | 30 | 40 |
20170801 | 20160301 | 20 | 30 | 40 | 50 |
20170801 | 20160401 | 30 | 40 | 50 | 60 |
20170801 | 20160501 | 40 | 50 | 60 | 70 |
20170801 | 20160601 | 50 | 60 | 70 | 80 |
20170801 | 20160701 | 60 | 70 | 80 | 90 |
20170801 | 20160801 | 70 | 80 | 90 | 100 |
20170801 | 20160901 | 80 | 90 | 100 | 110 |
20170801 | 20161001 | 90 | 100 | 110 | 120 |
20170801 | 20161101 | 100 | 110 | 120 | 130 |
20170801 | 20161201 | 110 | 120 | 130 | 140 |
20170801 | 20170101 | 120 | 130 | 140 | 150 |
20170801 | 20170201 | 130 | 140 | 150 | 160 |
20170801 | 20170301 | 140 | 150 | 160 | 170 |
20170801 | 20170401 | 150 | 160 | 170 | 180 |
One solution is:
tab1:
LOAD DATE_1,
DATE_2,
AMOUNT,
DATE_1&'@'&DATE_2 As Key1,
DATE_1&'@'&Date(AddMonths(Date#(DATE_2,'YYYYMMDD'),1),'YYYYMMDD') As Key2,
DATE_1&'@'&Date(AddMonths(Date#(DATE_2,'YYYYMMDD'),2),'YYYYMMDD') As Key3,
DATE_1&'@'&Date(AddMonths(Date#(DATE_2,'YYYYMMDD'),3),'YYYYMMDD') As Key4
FROM
[C:\Users\sarav\Downloads\TEST DATA.xlsx]
(ooxml, embedded labels, table is Sheet1);
Left Join(tab1)
LOAD Key1 As Key2, AMOUNT As [AMOUNT_NEXT DATE2]
Resident tab1;
Left Join(tab1)
LOAD Key1 As Key3, AMOUNT As [AMOUNT_NEXT DATE3]
Resident tab1;
Left Join(tab1)
LOAD Key1 As Key4, AMOUNT As [AMOUNT_NEXT DATE4]
Resident tab1;
Drop Field Key1, Key2, Key3, Key4;