Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi,
If you want to acheive on front end.
You can try with firstSortedValue()
Regards
It needs to be in the load script. Can you put "FirstSortedValue in the load script?
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
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