Loading records with the maximum value only

[#1]

Hi,

I'm having issues with my QlikView script.  I have no SQL expereince and am still a Qlikview novice and hence my question may seem basic.

When I try to run the following script I keep getting an error that says Quantity "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause".  Could someone please explain what I am missing, or point me in the right direction?

My Script:

LOAD

ItemCode,

warehouse,

transsubtype,

freefield1,

Quantity,

If(transsubtype='B' and Checked = '1' and bud_vers = 'MRP' and BlockItem='0', Quantity,

          If(transsubtype='H' and Checked = '1' and bud_vers = 'MRP' and BlockItem='0' and freefield1 = 'V',Quantity,

           If(transsubtype='J' and Checked = '1' and bud_vers = 'MRP' and BlockItem='0' and freefield1 = 'P',Quantity, 0)))

    AS QtyToBeDelivered,

If(transsubtype='A' and Checked = '1' and Reviewed = '1' and bud_vers = 'MRP' and BlockItem='0', Quantity,

          If(transsubtype='J' and Checked = '1' and Reviewed = '1' and bud_vers = 'MRP' and BlockItem='0' and freefield1 = 'B',Quantity,

          If(transsubtype='H' and Checked = '1' and Reviewed = '1' and bud_vers = 'MRP' and BlockItem='0' and freefield1 = 'P',Quantity, 0)))

    AS QtyToBeReceived,

GL_Acct;

SQL SELECT ItemCode, warehouse, transsubtype, freefield1, Quantity, Checked, bud_vers, BlockItem, GL_Acct

FROM dbo.TransactionTable

          GROUP BY warehouse, ItemCode, transsubtype, freefield1;

Many thanks in advance

C

[#2]

you need to do either:

only(Quantity),

or

sum(Quantity),

or

GROUP BY warehouse, ItemCode, transsubtype, freefield1, Quantity ;

[#3]

and the same with GL_Acct field

[#4]

Hi Pari Pari,

If you don't mind I want to take this right back to a basic example; how would I get a table with only 3 records for each of the items with only the highest price for that item?

ITEMS:

Load * inline [

Item, Colour, Supplier, Price

001, Red, ABC, 0.25

001, Blue, BCD, 0.30

001, Green, ASD, 0.15

002, Yellow, ABC, 0.48

002, Blue, DFG 0.37

002, Orange, SDE, 0.50

003, Red, ABC, 1.05

003, Yellow, SDF, 0.98

003, Blue, BCD, 1.05];

MainTable:

LOAD

          Item,

          Colour,

          Supplier,

          MAX(Price) as MaxPrice

Resident ITEMS Order by Item;

DROP Table ITEMS;

[Anonymous]

Hi, if not in need of colour/supplier you could reduce to:

MainTable:

LOAD
  Item,
  Max(Price) as MaxPrice
Resident ITEMS Group by Item;

Drop Table ITEMS;

Regards, Sander

[sebastiandperei · Accepted solution]

In first question, you are grouping by 4 fields, and reading a total of 9 fields. Always keep in mind, that when you use Group By, the rest of the fields of you are reading and not grouping must have an expression, some operation that you will make with the values of these fields. For example:

Table:

Load * Inline[

a     ,b

1     ,2

1     ,5

2     ,3];

Aux:

Load

     a,

     sum(b)

resident Table

group by a;

You will get the sum of the b field, for each value of a. In your example, why do you group? You want the sum of quantity for each combination of every value of the other fields? Try with sum(quantity) instead of quantity in SQL Select sentence. So, I think you need resolve the rest of the fields that you are reading in SQL Select sentence and not grouping or making an expression with them.

In second place, the script for the highest price per item should be:

ITEMS:

Load * inline [

Item, Colour, Supplier, Price

001, Red, ABC, 0.25

001, Blue, BCD, 0.30

001, Green, ASD, 0.15

002, Yellow, ABC, 0.48

002, Blue, DFG 0.37

002, Orange, SDE, 0.50

003, Red, ABC, 1.05

003, Yellow, SDF, 0.98

003, Blue, BCD, 1.05];

NoConcatenate

MainTable:

LOAD

          Item,

          MAX(Price) as MaxPrice

Resident ITEMS Order by Item

Group By Item;

Left Join

LOAD

     Item,

     Price as MaxPrice,

     Colour,

     Supplier

Resident ITEMS;

DROP Table ITEMS;

Like this way, you group the ITEMS table by item, then calculate the max value for each item. Afterwards, you join to that item with that price, the respective Colour and supplier.

[Anonymous]

Hi Sebastian, indeed the way to do it. Thx too!

Ps. taking that "Order by item" was to be left out.

[sebastiandperei]

Hi Sander,

I keep "Order by Item" because i think that cchhatralia needed like this way, ordered by item.

You could left out this, of course.

[#9]

Sebastian, that worked a treat!  Thank you!!