topic Problem with Group By in QlikView

Problem with Group By

Thu, 16 Sep 2010 08:35:21 GMT

Hi Everybody,

I need to find the maximum amount of qyantity ordered for each item.

i tried the following:

LOAD

Item

Max

(Quantity) as MaxQty;

Load

* From $(QvdPath)EntryTransaction.qvd (qvd) Group By Item;

Aggregation expressions required by GROUP BY clause
Load * From U:\QVD FILES\EntryTransaction.qvd (qvd) Group By Item

Any ideas ?

and i'm getting the following error:

Problem with Group By

vgutkovsky — Thu, 16 Sep 2010 08:39:57 GMT

It's a little difficult to read your code with the spacing and comments interspersed. But it should work if you do it this way:


data:
LOAD * FROM EntryTransaction.qvd (qvd);

data1:
LOAD
 Item,
 max(Quantity) as MaxQty
RESIDENT data
GROUP BY Item;

You can then join data1 into data, or leave it a separate table, your choice.

Regards,

Problem with Group By

Thu, 16 Sep 2010 08:41:01 GMT

Hi,

Not getting your script extract.

Plz share sample data.

Problem with Group By

Sun, 19 Sep 2010 08:03:08 GMT

Hi,

Thanks a lot - it works!!!

but my question is - I can't use group by directly on the qvd? i must use it on resident ?

another thing - I'm sorry about how the script looks in the forum - from some reason, it doesn't copy ok.

Problem with Group By

Sun, 19 Sep 2010 14:02:16 GMT

Its always more efficient to load a qvd into memory (resident) before doing any data transformation as the data is then loaded 'optimised'.

Also see the pinned post ' How to add Code to your posts' which shows how to post code. Personally I just copy it into notepad then copy and paste from there.

Regards,

Gordon