Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

r_vdbraak
New Contributor III

FirstSortedValue with double values

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-201411,91
16-04-201411,95
15-04-201411,84
14-04-201411,75
11-04-201411,83
10-04-201411,76
09-04-201411,77
08-04-201411,75
07-04-201411,94

Thanks for thinking with me.

1 Solution

Accepted Solutions
r_vdbraak
New Contributor III

Re: FirstSortedValue with double values

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;

5 Replies
MVP
MVP

Re: FirstSortedValue with double values

Try using distinct like:

LOAD

  FirstSortedValue( Distinct Date, Koers) AS DateMinKoers

Don't worry about syntax error red mark.

r_vdbraak
New Contributor III

Re: FirstSortedValue with double values

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

MVP
MVP

Re: FirstSortedValue with double values

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;

r_vdbraak
New Contributor III

Re: FirstSortedValue with double values

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;

Not applicable

Re: FirstSortedValue with double values

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

Regards

Darek

Community Browser