Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

lookup min value in range

I'm looking for a SQL or Qlikview function to lookup the the min. value for ExRate for a specific Date.
For example if the Date in table b = 20050509 the value 0,12310 from table a must be returned.

Table a:

DateExRateValuta
199901010,11984NOK
200207010,13434NOK
200411040,12274NOK
200412010,12258NOK
200502010,12060NOK
200503020,12204NOK
200505020,12310NOK
200506010,12522NOK
200507010,12653NOK
200508010,12658NOK
200509010,12650NOK
200510040,12700NOK
200511030,12846NOK
200512010,12578NOK
200601050,12621NOK


How can this be solved in SQL or with a Qlikview function?

Best regards,

Johann

8 Replies
Not applicable
Author

Hi Johann, are you trying to do this in the script or in a chart?

In script you could create a table with the SQL command "group by" that just contains the Date, Currency and min. value or in a chart you can do this by using min(ExRate) if "Date" is the dimension.

Cheers, Lukas

Not applicable
Author

I try to do this in the script.

I want to use the Date from table b and look for the nearest (min) Date in table a and then return the value from ExRate, to use in table b.

So if b.Date is 20050509 then the nearest (min) a.Date = 20050502 and the value for ExRate = 0,12310.

The result of the query/script must be :

b.Dateb.ExRate
200505090,1231


Regards, Johann

fseregaza
Partner - Contributor III
Partner - Contributor III

Hi Johann,

Have you tried the FirstSortedValue() function ? Maybe it will fit you.

Best regards,

Franck SEREGAZA

Business & Decision

Not applicable
Author

Hi Franck,

I want to solve this within the SQL statement, so I can use this field in other calculations.
Do you have any suggestions how to solve this?

Best regards,

Not applicable
Author

I don't see your example date in your table. Are you looking for the minimum value for anything up to the date specified?

Not applicable
Author

Aaron, the example is described earlier in this post. For example I have date 20090509.
The result must be 20090502 (the nearest min(date) and the value that belongs to this date (ExRate).

Not applicable
Author

Do you have a sample of the data that will be making this reference? There is a function that I think will work for this, but it will have to incorporate sorting and a join to make it work.

Not applicable
Author

I already solved this with a subquery in combination with joins.

Thanks.

Best regards,

Johann