topic Invalid Function error in Preceding Load in QlikView

Invalid Function error in Preceding Load

Tue, 29 Nov 2016 03:28:20 GMT

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?

Re: Invalid Function error in Preceding Load

tamilarasu — Tue, 29 Nov 2016 03:45:31 GMT

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

Re: Invalid Function error in Preceding Load

Anonymous — Wed, 30 Nov 2016 12:54:21 GMT

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.

Re: Invalid Function error in Preceding Load

rubenmarin — Wed, 30 Nov 2016 12:59:16 GMT

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;