Exchange rate

[bharatkishore]

    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

[tresesco]

Have you tried like:

Load

          *

From <> where Month=201703;

[sunny_talwar]

What is the logic behind picking 201703? Will this never change or are you always looking to pull in Max period minus one month?

[bharatkishore]

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.

[bharatkishore]

Thanks Tresesco, i thought too much but forget where condition.. thank you..

[sunny_talwar · Accepted solution]

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;

[bharatkishore]

Thank you Sunny Bhai...Thanks a lot...