Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Load sales by max priority

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.

9 Replies
sunny_talwar

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;

saimahasan
Partner - Creator III
Partner - Creator III

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.

Anonymous
Not applicable
Author

Thanks)

I'm going to check it tomorrow)

Anonymous
Not applicable
Author

Thanks for advice!

Anonymous
Not applicable
Author

i've checked it.

It works more slowly, than group by operation

sunny_talwar

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

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

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