Problem with Group By

[#1]

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:

[vgutkovsky]

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,

[#3]

Hi,

Not getting your script extract.

Plz share sample data.

[#4]

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.

[#5]

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