Load "Maximum" values

[nwilliams]

Hi,

I have a set of data with "duplicate" values in the same field.  I want load the latest of these values based on the date it was created.

I have tried using the "Max" function in the load script, but unable to get this working.  The script I have looks like this:

LOAD

     metric_instance.id,

     MAX(metric_instance.sys_created_on) AS maxcreated,

     metric_instance.value

FROM

(qvd)

GROUP BY metric_instance.id, metric_instance.value;

The field "metric_instance.id" is the field with the duplicated, and "metric_instance.sys_created_on" is the date field I want the latest record for.

Can anyone offer any advice?

[PrashantSangle]

Hi,

If you want to acheive on front end.

You can try with firstSortedValue()

Regards

[nwilliams]

It needs to be in the load script.  Can you put "FirstSortedValue in the load script?

[PrashantSangle]

Hi,

You can try it in script also

Like

LOAD metric_instance.id,

     FirstSortedValue(metric_instance.value,metric_instance.sys_created_on) AS maxcreated

FROM

(qvd)

GROUP BY metric_instance.id

Regards

[Anonymous]

Hi Williams,

First you should load the table and set the date field in increasing order. as below-

Test:

LOAD

     metric_instance.id,

     metric_instance.sys_created_on as date,

     metric_instance.value

FROM

(qvd)

noconcatenate

Test1:

Load *

resident Test;

order by date;

drop table Test;

Noconcatenate

Test2:

LOAD

     metric_instance.id,

     date,

     metric_instance.value

where date > old date

  GROUP BY metric_instance.id, metric_instance.value,date;

drop table Test1;

Hope this would be helpful for u.

Thanks