topic Re: Need help with Load/SQL script in QlikView

Loading records with the maximum value only

Fri, 31 Aug 2012 10:38:29 GMT

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

Re: Need help with Load/SQL script

Fri, 31 Aug 2012 12:01:55 GMT

you need to do either:

only(Quantity),

sum(Quantity),

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

Re: Need help with Load/SQL script

Fri, 31 Aug 2012 12:02:51 GMT

and the same with GL_Acct field

Re: Need help with Load/SQL script

Fri, 31 Aug 2012 14:31:57 GMT

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;

Re: Need help with Load/SQL script

Anonymous — Sat, 01 Sep 2012 12:01:08 GMT

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

Re: Need help with Load/SQL script

sebastiandperei — Sat, 01 Sep 2012 14:51:00 GMT

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

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.

Re: Need help with Load/SQL script

Anonymous — Sat, 01 Sep 2012 15:18:58 GMT

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

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

Re: Need help with Load/SQL script

sebastiandperei — Sat, 01 Sep 2012 17:31:51 GMT

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.

Re: Need help with Load/SQL script

Mon, 03 Sep 2012 08:17:21 GMT

Sebastian, that worked a treat! Thank you!!