topic Re: Load sales by max priority in QlikView

Load sales by max priority

Anonymous — Mon, 12 Sep 2016 13:38:36 GMT

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.

Re: Load sales by max priority

sunny_talwar — Mon, 12 Sep 2016 13:42:40 GMT

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;

Re: Load sales by max priority

saimahasan — Mon, 12 Sep 2016 13:48:13 GMT

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.

Re: Load sales by max priority

Anonymous — Mon, 12 Sep 2016 13:56:42 GMT

Thanks)

I'm going to check it tomorrow)

Re: Load sales by max priority

Anonymous — Mon, 12 Sep 2016 13:57:28 GMT

Thanks for advice!

Re: Load sales by max priority

Anonymous — Tue, 13 Sep 2016 05:05:09 GMT

i've checked it.

It works more slowly, than group by operation

Re: Load sales by max priority

sunny_talwar — Tue, 13 Sep 2016 05:16:25 GMT

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

Re: Load sales by max priority

jonathandienst — Tue, 13 Sep 2016 06:25:57 GMT

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.

Re: Load sales by max priority

jonathandienst — Tue, 13 Sep 2016 06:30:22 GMT

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));

Re: Load sales by max priority

Anonymous — Tue, 13 Sep 2016 13:09:48 GMT

Don't understand, how i can use these code? For already sorted data?