Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
tan_chungkam
Contributor

Always show the latest Rate

Hi all,

I have a excel table as below:

image.pngTable

I would like to do in scripting level to always show it's latest exchange rate.

Please find below as my expected outcome:

image.pngExpected Outcome

Labels (2)
5 Replies
MVP
MVP

Re: Always show the latest Rate

Use a pattern like this to fill in missing rates with the most recent:

LOAD Year,
	Period,
	[Base Currency],
	If([Base Currency] = Previous([Base Currency]), 
		Alt(Rate, Peek(Rate)),
		Rate) as Rate
FROM ...
Order by [Base Currency],Year, Period;

 Please post your data as a text table rather than an image. That allows for testing and validation of the solution.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Channa
Valued Contributor III

Re: Always show the latest Rate

i think he is looking for single row 

where year =year(today()) and month=max(month)

Channa
tan_chungkam
Contributor

Re: Always show the latest Rate

@jontydkpi ,

Thanks for the help.

However, it doesn't work on it.

What I'm looking for is the previous rate should always same as the rate of  the latest year period.

For e.g. AUD rate in Year (2019) &  Period (1) is 1.2. When it comes to Year (2019) & Period (2), the AUD rate is 1.3. Thus, the AUD rate in Year (2019) & Period (1) should change to 1.3.

Please find attached as my excel file.

Really appreciate your time spending and work. Thanks a million.

Channa
Valued Contributor III

Re: Always show the latest Rate

irrespective of period selection..  it should show latest rate ??

is it correct?

Channa
tan_chungkam
Contributor

Re: Always show the latest Rate

hi @Channa ,

Yes, it is.

To put it in simply,  the result should be same as the above screenshot.

It mean the AUD rate was 1.617314 in Year 2019 Period 1 but it should be the latest rate - 1.5966 after the scripting.