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!!
