Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have the following code:
Currency | Month | Rate |
USD | 201701 | 1 |
USD | 201702 | 2 |
USD | 201703 | 3 |
USD | 201704 | 4 |
ASD | 201701 | 5 |
ASD | 201702 | 6 |
ASD | 201703 | 7 |
ASD | 201704 | 8 |
Now i need to get the rate where my month is 201703. Output should be:
USD 201703 3
ASD 201703 7
Can you please help me how can i achieve it. I need to do in script.
Thanks,
Bharat
Try this
Table:
LOAD Currency,
Date#(Month, 'YYYYMM') as Month,
Rate;
LOAD * INLINE [
Currency, Month, Rate
USD, 201701, 1
USD, 201702, 2
USD, 201703, 3
USD, 201704, 4
ASD, 201701, 5
ASD, 201702, 6
ASD, 201703, 7
ASD, 201704, 8
];
Right Join (Table)
LOAD AddMonths(Max(Month), -1) as Month
Resident Table;
Have you tried like:
Load
*
From <> where Month=201703;
What is the logic behind picking 201703? Will this never change or are you always looking to pull in Max period minus one month?
Sunny bhai,
I need to pull max of period minus one month as of now, but later it will be same month i.e. for ex 201805.
Thanks Tresesco, i thought too much but forget where condition.. thank you..
Try this
Table:
LOAD Currency,
Date#(Month, 'YYYYMM') as Month,
Rate;
LOAD * INLINE [
Currency, Month, Rate
USD, 201701, 1
USD, 201702, 2
USD, 201703, 3
USD, 201704, 4
ASD, 201701, 5
ASD, 201702, 6
ASD, 201703, 7
ASD, 201704, 8
];
Right Join (Table)
LOAD AddMonths(Max(Month), -1) as Month
Resident Table;
Thank you Sunny Bhai...Thanks a lot...