Invalid Function error in Preceding Load

[#1]

I am trying to load a sales transaction table. For each TxnID, there can be many items, and each item has its own quantity (e.g. someone can buy five loaves of bread and six sausages in one transaction). I want to find the maximum number of any item  (i.e. here, I want "6" for the sausages.)

My script looks like this:

Load

     *,

     max(ItemQty) as MaxItem

GROUP BY TxnID;

Load

     TxnID

     ItemCost

     ItemQty,

     etc.

from

    C:\MyQvd.QVD (qvd); 

However, I get an "Invalid Expression" error, and the table does not load. I also needed another calculated field, and this works:

Load

     *,

     ItemQty * ItemCost AS ItemExtendedCost

;

Load

     TxnID

     ItemCost

     ItemQty,

     etc.

from

    C:\MyQvd.QVD (qvd); 

My understanding was if you use an aggregation in a preceding LOAD (sum, max, etc.), you must include a GROUP BY in that LOAD statement.

Is my understanding incorrect? Is my syntax incorrect? Or is there something else I am missing?

[tamilarasu]

Hi Kevin,

Group by statment should have all the field names except the one you used for aggregation.

In your case the syntax should be like below

Load TxnID

        ItemCost,

        Max(ItemQty) as MaxItem

        etc.

GROUP BY TxnID, ItemCost, etc;    \\Except ItemQty field

Load

     TxnID

     ItemCost

     ItemQty,

     etc.

from

    C:\MyQvd.QVD (qvd);

[Anonymous]

Hi Kevin,

As Tamil Nagaraj has mentioned, while performing an aggregation in load script, one needs to apply group by with all the fields used in the preceding load statement. On the other hand a simple multiplication or division doesn't need a group by clause and so works fine the way it is.

[rubenmarin]

Hi Kevin, maybe you can try to add the max value using a join (or applymap)

Table1:

Load

     TxnID

     ItemCost

     ItemQty,

     etc.

from

    C:\MyQvd.QVD (qvd);

Left Join

Load TxnID,

     max(ItemQty) as MaxItem

Resident Table1

GROUP BY TxnID;