topic Re: FirstSortedValue is returning NULL values in QlikView

FirstSortedValue is returning NULL values

Tue, 23 Jul 2013 01:46:47 GMT

Hi,

This is my first post. I hope I can give as much details as possible. Below is a tiny sample of the data.

Date Storage Litres

01/05/13 Tank1 10500

03/05/13 Tank2 9000

03/05/13 Tank1 8000

05/05/13 ServiceTruck 2500

What I need to achieve is for each Storage, i need their very first reading at the beginning of each month.

I have tried to use FirstSortedValue but for some reason I get null values under litres:

Load

Monthname(Date) as DipMonth,

Storage,

Litres as Dip_litres,

FirstSortedValue(Litres, Monthname(Date)) as Dip_litres1

RESIDENT Fuel_Dip

Group by

Monthname(Date),

Storage;

Can anyone show me what is the best way of getting the first reading for each month for each Storage tank/truck please?

Thank you

Re: FirstSortedValue is returning NULL values

rwunderlich — Tue, 23 Jul 2013 05:10:11 GMT

Your FisrtSortedValue shoud reference the Date, not the month:

FirstSortedValue(Litres, Date) as Dip_litres1

-Rob

Re: FirstSortedValue is returning NULL values

Tue, 23 Jul 2013 05:20:46 GMT

see the attached file and example of firstsortedvalue.

test:

LOAD * INLINE [

employee, time, status, rank

Fred, 09:00, In, 1

Fred, 09:55, Out, 2

Jo, 08:55, In, 1

Bert, 08:40, In, 1

Bert, 12:40, Out, 2

Bert, 14:40, In, 3

];

LOAD

employee as employeenew,

max(time(time,'hh:mm')) as maxtime ,

FirstSortedValue(status,-time(time,'hh:mm')) as statusnew,

FirstSortedValue(rank,-time(time,'hh:mm')) as ranknew

Resident test Group by employee;

DROP Table test;

Re: FirstSortedValue is returning NULL values

Tue, 23 Jul 2013 05:37:58 GMT

Hi Rob and Vishwaranjan,

Thank you both for your response.

This is what I used and it works:

FirstSortedValue(DISTINCT Litres, Monthname(Date)) as Dip_litres1

somehow it works.