Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Valued Contributor

Re: Need help with Load/SQL script

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.

8 Replies
Not applicable

Re: Need help with Load/SQL script

you need to do either:

only(Quantity),

or

sum(Quantity),

or

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

Not applicable

Re: Need help with Load/SQL script

and the same with GL_Acct field

Not applicable

Re: Need help with Load/SQL script

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;

filosofo
Contributor

Re: Need help with Load/SQL script

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
Valued Contributor

Re: Need help with Load/SQL script

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.

filosofo
Contributor

Re: Need help with Load/SQL script

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

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

sebastiandperei
Valued Contributor

Re: Need help with Load/SQL script

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

Re: Need help with Load/SQL script

Sebastian, that worked a treat!  Thank you!!