FirstSortedValue Question?

[buddhabang]

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.

[swuehl · Accepted solution]

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

[IAMDV]

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,

DV

[buddhabang]

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.