topic Re: Load "Maximum" values in QlikView

Load "Maximum" values

nwilliams — Tue, 16 Sep 2014 07:11:54 GMT

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?

Re: Load "Maximum" values

PrashantSangle — Tue, 16 Sep 2014 07:15:41 GMT

Hi,

If you want to acheive on front end.

You can try with firstSortedValue()

Regards

Re: Load "Maximum" values

nwilliams — Tue, 16 Sep 2014 07:19:33 GMT

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

Re: Load "Maximum" values

PrashantSangle — Tue, 16 Sep 2014 07:20:45 GMT

Hi,

You can try it in script also

LOAD metric_instance.id,

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

FROM

(qvd)

GROUP BY metric_instance.id

Regards

Re: Load "Maximum" values

Anonymous — Tue, 16 Sep 2014 07:24:01 GMT

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