topic lookup min value in range in QlikView

lookup min value in range

Thu, 11 Feb 2010 10:11:11 GMT

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:

Date	ExRate	Valuta
19990101	0,11984	NOK
20020701	0,13434	NOK
20041104	0,12274	NOK
20041201	0,12258	NOK
20050201	0,12060	NOK
20050302	0,12204	NOK
20050502	0,12310	NOK
20050601	0,12522	NOK
20050701	0,12653	NOK
20050801	0,12658	NOK
20050901	0,12650	NOK
20051004	0,12700	NOK
20051103	0,12846	NOK
20051201	0,12578	NOK
20060105	0,12621	NOK

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

Best regards,

Johann

lookup min value in range

Thu, 11 Feb 2010 10:21:08 GMT

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

lookup min value in range

Thu, 11 Feb 2010 10:34:57 GMT

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.Date	b.ExRate
20050509	0,1231

Regards, Johann

Re. :Re: lookup min value in range

fseregaza — Thu, 11 Feb 2010 16:57:17 GMT

Hi Johann,

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

Best regards,

Franck SEREGAZA

Business & Decision

Re. :Re: lookup min value in range

Mon, 15 Feb 2010 15:24:10 GMT

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,

lookup min value in range

Mon, 15 Feb 2010 16:27:34 GMT

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

lookup min value in range

Mon, 15 Feb 2010 16:35:48 GMT

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).

lookup min value in range

Mon, 15 Feb 2010 16:58:38 GMT

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.

lookup min value in range

Tue, 16 Feb 2010 08:18:32 GMT

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

Thanks.

Best regards,

Johann