8 Replies Latest reply: Sep 3, 2012 4:17 AM by Chirag Chhatralia RSS

    Loading records with the maximum value only

    Chirag Chhatralia

      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

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

          • Re: Need help with Load/SQL script

            and the same with GL_Acct field

              • Re: Need help with Load/SQL script
                Chirag Chhatralia

                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
                    Sander Janssen

                    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
                        Sebastian Pereira

                        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.