Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

FirstSortedValue is returning NULL values

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


3 Replies

Re: FirstSortedValue is returning NULL values

Your FisrtSortedValue shoud reference the Date, not the month:

FirstSortedValue(Litres, Date) as Dip_litres1

-Rob

Not applicable

Re: FirstSortedValue is returning NULL values

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;

Not applicable

Re: FirstSortedValue is returning NULL values

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.

Community Browser