FirstSortedValue Load Script Optimization

Dayna — Thu, 24 Sep 2015 09:37:21 GMT

Hello all,

I'm really hoping you can help me.. I'm using a data source within our environment that has hundred of thousand of records. Pulling in the records into QlikView only takes about 5 minutes which I then store into a QVD.

The problem comes, when I need to manipulate this data. As I need to get the last record for a given date by part and reference, I use 'FirstSortedValue' on the records. My load script then looks something like this:

LOAD

FirstSortedValue("tr_addr",-tr_trnbr) as tr_addr,

FirstSortedValue("tr_domain",-tr_trnbr) as tr_domain,

FirstSortedValue(tr_effdate,-tr_trnbr) as tr_effdate,

tr_recorded_date,

FirstSortedValue("tr_loc",-tr_trnbr) as tr_loc,

FirstSortedValue("tr_loc_begin",-tr_trnbr) as tr_loc_begin,

FirstSortedValue("tr_loc_end",-tr_trnbr) as tr_loc_end,

FirstSortedValue("tr_lot",-tr_trnbr) as tr_lot,

FirstSortedValue("tr_nbr",-tr_trnbr) as tr_nbr,

upper("tr_part") as tr_part,

FirstSortedValue("tr_qty_loc",-tr_trnbr) as tr_qty_loc,

"tr_ref",

FirstSortedValue("tr_serial",-tr_trnbr) as tr_serial,

FirstSortedValue("tr_site",-tr_trnbr) as tr_site,

FirstSortedValue("tr_status",-tr_trnbr) as tr_status,

FirstSortedValue(tr_time,-tr_trnbr) as tr_time,

FirstSortedValue("tr_trnbr",-tr_trnbr) as tr_trnbr,

FirstSortedValue("tr_type",-tr_trnbr) as tr_type,

if(isnull(tr_ref)=0,tr_ref,FirstSortedValue("tr_serial",-tr_trnbr)) as tr_identifier

FROM

(qvd)

GROUP BY

tr_recorded_date,

tr_part,

tr_ref;

The problem is, when I was looking at a few thousand records it would take 10 minutes, when I'm looking at a lot more we're talking over four hours to load the report.

Do you have any ideas how I can optimize it? Or an alternative way of achieving the same results?

Any help you can offer would be greatly appreciated, as I'm getting a bit stuck!!

Kind Regards,

Dayna

topic FirstSortedValue Load Script Optimization in QlikView

FirstSortedValue Load Script Optimization