topic FirstSortedValue Question? in QlikView

FirstSortedValue Question?

buddhabang — Tue, 06 Dec 2011 15:28:14 GMT

Hi there,

Just wanted to check if anyone can help on the following issue i have here -

I work for a water company and we have meters with serial_numbers and the dates they were installed - Note: a property can have numerous meter_serials.

Fields: - Meter_Serial_Number

- Created_On

I have this following expression FIRSTSORTEDVALUE(Meter_Serial_Number, -CreatedOn) in a chart box expression field, hoping to bring out all the meter_serial_numbers with their respective date they were installed in one single row.

Ufortunately i can't get it to work, any help please would be much appreciated.

FirstSortedValue Question?

swuehl — Tue, 06 Dec 2011 16:27:44 GMT

What is your dimension in your chart?

Do you really want to show the results in one row? Then maybe you need to look into concat() function (this function takes also a sort weight as third parameter).

Otherwise, I think your expression should work if you set an appropriate dimension. FirstSortedValue will return only one value or NULL, if the result is ambiguous.

So maybe just a table with dimension Meter_Serial_Number and Expression only(CreatedON) is what you are looking for?

Regards,

Stefan

Re: FirstSortedValue Question?

IAMDV — Tue, 06 Dec 2011 16:31:16 GMT

Hi,

I am assuming that you wanted to see the last value, because you are preceding the sort value with a minus sign. What does it return? If you have more than value then it returns NULL value. In this case you need to use AGGR() function to group the data. You can also use Total qualifier to disregard the chart dimension, although Total qualifier still works with your current selection.

Please post the example if this doesn’t help.

Thanks,

FirstSortedValue Question?

buddhabang — Wed, 07 Dec 2011 09:34:52 GMT

Thanks guys, i managed to get it work but the only down side is, it takes such a long time to run.

Here's how i worked it out, concatenating the

FirstSortedValue(Serial_Number, -Created_On) &','&date(FirstSortedValue(Installation_Date, -Created_On)) &','&

FirstSortedValue(Serial_Number, -Created_On,2) &','&date(FirstSortedValue(Installation_Date, -Created_On,2)) &','& (etc....)

This works fine, and will only privide one row of data

Meter_Serial Date Meter_Seral 2 Dat2

23M3JIK 01/05/2011 52J3OPL 15/06/2010 etc....

Thanks for your help all.