Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading records with the maximum value only

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

1 Solution

Accepted Solutions
sebastiandperei
Specialist
Specialist

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.

View solution in original post

8 Replies
Not applicable
Author

you need to do either:

only(Quantity),

or

sum(Quantity),

or

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

Not applicable
Author

and the same with GL_Acct field

Not applicable
Author

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
Not applicable
Author

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
Specialist
Specialist

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
Not applicable
Author

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

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

sebastiandperei
Specialist
Specialist

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.

Not applicable
Author

Sebastian, that worked a treat!  Thank you!!