Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone!
I have some simple load from xls.
Second operation - saving rows with max Priority field by group by function.
My script:
Main:
LOAD DeliveryPoint,
[Product type],
Agent,
Priority,
Sales
FROM
[Max priority sales.xlsx]
(ooxml, embedded labels, table is Лист1);
right Join(Main)
load
DeliveryPoint,
[Product type],
max(Priority) as Priority
Resident Main
Group by DeliveryPoint,
[Product type];
It works fine, but in large volume data group by function take too much time (more one hour).
Colleagues, is in QV some others ways to keep sales by following logic?
Source file and model is attached.
Thanks.
See if this is any faster
Main:
LOAD AutoNumber(DeliveryPoint&[Product type]) as Key,
DeliveryPoint,
[Product type],
Agent,
Priority,
Sales
FROM
[Max priority sales.xlsx]
(ooxml, embedded labels, table is Лист1);
FinalMain:
NoConcatenate
LOAD *
Resident Main
Where Key <> Previous(Key)
Order By Key, Priority desc;
DROP Table Main;
in order to implement the same logic, u dont think we can exclude group by. But yes you can try with having a left join rather than having a right join and see whether it reduces your time.
Thanks)
I'm going to check it tomorrow)
Thanks for advice!
i've checked it.
It works more slowly, than group by operation
I don't know if there is a faster way to calculated max using a method mentioned here
“Fastest” Method to Read max(field) From a QVD | Qlikview Cookbook. I have added a comment at the bottom for rwunderlich. Hopefully he can guide us for a way to do this.
Best,
Sunny
I don't think there is a faster way by playing only with the script. If however, the data source can be sorted, then you can perform a sorted read and get the max values in a single pass:
Main:
LOAD AutoNumber(DeliveryPoint&[Product type]) as Key,
DeliveryPoint,
[Product type],
Agent,
Priority,
If(
DeliveryPoint = Previous(DeliveryPoint)
And [Product type] = Previous([Product type]),
RangeMax(Peek(MaxPriority), Priority),
Priority
) as MaxPriority,
Sales
FROM [Max priority sales.xlsx]
(ooxml, embedded labels, table is ????1);
This assumes that the data source is sorted by DeliveryPoint and [Product type] (either ascending or descending) and by Priority in descending order.
Reread your OP and I see you want to filter for the maximum priority values...
Well if the data can be sorted at source again, then
Main:
LOAD AutoNumber(DeliveryPoint&[Product type]) as Key,
DeliveryPoint,
[Product type],
Agent,
Priority,
Sales
FROM [Max priority sales.xlsx]
(ooxml, embedded labels, table is ????1)
Where Not(Exists(Key));
Don't understand, how i can use these code? For already sorted data?