topic Re: Exchange rate in QlikView

Exchange rate

bharatkishore — Thu, 31 May 2018 10:22:01 GMT

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

Re: Exchange rate

tresB — Thu, 31 May 2018 10:30:27 GMT

Have you tried like:

Load

From <> where Month=201703;

Re: Exchange rate

sunny_talwar — Thu, 31 May 2018 11:11:36 GMT

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

Re: Exchange rate

bharatkishore — Thu, 31 May 2018 11:23:32 GMT

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.

Re: Exchange rate

bharatkishore — Thu, 31 May 2018 11:25:04 GMT

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

Re: Exchange rate

sunny_talwar — Thu, 31 May 2018 11:27:23 GMT

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;

Re: Exchange rate

bharatkishore — Thu, 31 May 2018 11:37:39 GMT

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