topic FirstSortedValue with double values in QlikView

FirstSortedValue with double values

r_vdbraak — Wed, 23 Apr 2014 07:57:57 GMT

Hi,

Who can help me solving this problem.

I'm using a script to select the value of the lowest Koers and the highest Koers

It works correct as long there are no double Koers values

If there is a double Value I want to select the latest date.

DateMaxKoers

LOAD

FirstSortedValue(Date, -Koers) AS DateMaxKoers

Resident Koersen;

LOAD

FirstSortedValue(Date, Koers) AS DateMinKoers

Resident Koersen;

I expect as DateMaxKoers 16-04-2014 11,95

as DateMinKoers 14-04-2014 11,75

Date	Koers
17-04-2014	11,91
16-04-2014	11,95
15-04-2014	11,84
14-04-2014	11,75
11-04-2014	11,83
10-04-2014	11,76
09-04-2014	11,77
08-04-2014	11,75
07-04-2014	11,94

Thanks for thinking with me.

Re: FirstSortedValue with double values

tresB — Wed, 23 Apr 2014 08:24:38 GMT

Try using distinct like:

LOAD

FirstSortedValue( Distinct Date, Koers) AS DateMinKoers

Don't worry about syntax error red mark.

Re: FirstSortedValue with double values

r_vdbraak — Wed, 23 Apr 2014 08:31:00 GMT

Treseco,

Thanks for your helpful thinking.

Using Distinct gives me a value, but it is the first date (08-04-2014) in stead of the last date (14-04-2014)

Gr. Richard

Re: FirstSortedValue with double values

tresB — Wed, 23 Apr 2014 08:35:16 GMT

User the Order By clause to change the order of load , like:

Temp:

Load * Inline [

Date, Koers
17-04-2014, 11.91
16-04-2014, 11.95
15-04-2014, 11.84
14-04-2014, 11.75
11-04-2014, 11.83
10-04-2014, 11.76
09-04-2014, 11.77
08-04-2014, 11.75
07-04-2014, 11.94
];
Fin:
Load
FirstSortedValue(distinct Date, Koers) as FSV
Resident Temp order by Date asc;

Re: FirstSortedValue with double values

r_vdbraak — Wed, 23 Apr 2014 08:44:22 GMT

Treseco,

When I use Order by Date desc I got what I expect.

Thanks for your help.

Gr. Richard

The complete script:

LOAD

FirstSortedValue(Date, Koers) AS DateMinKoers

Resident Koersen

Order by Date desc;

Re: FirstSortedValue with double values

Wed, 23 Apr 2014 08:51:34 GMT

Example for you with calculations in the script and on the fly.

Regards

Darek