12 Replies Latest reply: Jun 29, 2017 5:05 AM by Ruslans KLimovs RSS

    Sum by creteria

    Ruslans KLimovs

      Hello, I have such results from SQL select:

       

      Client nr.     Device Type     Device nr.

      Client 1          Type_A          12

      Client 1          Type_A          13

      Client 1          Type_B          14

      Client 2          Type_C          16

       

      I need to create report table, where I can see, how much devices of each type has each client:

       

      Client nr.     Device Type          Quantity

      Client 1          Type_A               2

      Client 1          Type_B               1

      Client 2          Type_C               1

       

       

      How can I create necessary expression?

       

      Thanks in advance!

        • Re: Sum by creteria
          Jaume Criballés Faja

          Here you have one solution:

           

          test:

          LOAD * INLINE [

              Client nr.,     Device Type,     Device nr.

          Client 1 ,         Type_A  ,        12

          Client 1 ,         Type_A  ,        13

          Client 1  ,        Type_B  ,        14

          Client 2 ,         Type_C    ,      16

          ];

           

           

          test2:

          load

          [Client nr.],

          [Device Type],

          count([Device nr.]) as Quantity

          Resident test

          Group by [Client nr.],[Device Type];

           

           

          drop table test;

          • Re: Sum by creteria
            Jaume Criballés Faja

            Then create a table like this:

             

            Two dimensions: Client nr. and Device Type

            One expression: count([Device nr.])


            Captura.PNG

              • Re: Sum by creteria
                Ruslans KLimovs

                I do something wrong with a syntax

                 

                 

                 

                TEST:

                LOAD

                FA_Number,

                FA_Location_Subcode,

                FA_Class

                ;

                 

                TEST2:

                LOAD

                FA_Location_Subcode,

                FA_Class,

                COUNT(FA_Number) AS Quantity_Total

                 

                Resident TEST

                Group by

                FA_Location_Subcode,

                FA_Class;

                DROP TABLE TEST;

                 

                 

                SQL SELECT

                TABLE_FIXED_ASSETS."No_" AS FA_Number,

                TABLE_FIXED_ASSETS."FA Location SubCode" AS FA_Location_Subcode,

                 

                TABLE_FACLASS.Code AS FA_Class

                 

                FROM *********** AS TABLE_FIXED_ASSETS

                 

                error.jpg

                  • Re: Sum by creteria
                    Jaume Criballés Faja

                    Forget what I said in my first comment about to create a new table called : test2 using group by. You don't need to do that.

                     

                    Create a table as I said in the second commentary with the data that you get directly from your SQL Data Base.

                     

                    I attach a .qvw that will help you.

                      • Re: Sum by creteria
                        Ruslans KLimovs

                        Stil can you please specify, how I can use LOAD in my case. I will use it in many other situations.

                          • Re: Sum by creteria
                            Jaume Criballés Faja

                            I don't undertand what you mean. You want to know how create a table using group by in your script?

                              • Re: Sum by creteria
                                Ruslans KLimovs

                                I want to get idea about LOAD from Resident Load.

                                  • Re: Sum by creteria
                                    Jaume Criballés Faja

                                    Basically the Resident Load is used to load a table that you have already loaded in the script in a new table.

                                     

                                    In this case you have the table test loaded from the SQL data base. Then you want to do some transformation using the data from the table test. Then you use the Resident to point that table test and start the transformation in a new table without modifying the original table.

                                     

                                    Here you have a link that provides more information : QlikView Resident Load

                                      • Re: Sum by creteria
                                        Ruslans KLimovs

                                        Thank you, I already have got the idea, but can't get in with the syntax. Why I got error.... Why "Table not found"?

                                         

                                        DATASOURCE:

                                        SQL SELECT * from DB;

                                         

                                        TABLE2:

                                             LOAD

                                                  FA_Number,

                                                  FA_Location_Subcode,

                                                  FA_Class

                                             RESIDENT DATASOURCE;

                                         

                                        TEST2:

                                             LOAD

                                                  FA_Location_Subcode,

                                                  FA_Clas

                                             RESIDENT TABLE2;

                                          • Re: Sum by creteria
                                            Jaume Criballés Faja

                                            I think that the problem is that Datasource table and Table2 table will have exactly the same Information and then Qlikview automatically concatenates the table2 inside datasource table. Then as table2 has been concatenated inside datasource, table2 has dissapeared. Then when it tries to do a resident table2 is failing because this table doesn't exist.

                                             

                                            You have two solutions:

                                             

                                            Solution 1 (disable the automatic concatenation using the function NoConcatenate):

                                             

                                            DATASOURCE:

                                            SQL SELECT * from DB;

                                             

                                            NoConcatenate

                                            TABLE2:

                                                 LOAD

                                                      FA_Number,

                                                      FA_Location_Subcode,

                                                      FA_Class

                                                 RESIDENT DATASOURCE;

                                             

                                            TEST2:

                                                 LOAD

                                                      FA_Location_Subcode,

                                                      FA_Clas

                                                 RESIDENT TABLE2;

                                             

                                            Drop Table TABLE2;

                                             

                                            Solution 2 (In this case you can directly use the datasource table for the resident. The intermediate table Table2 is not necessary):

                                             

                                            DATASOURCE:

                                            SQL SELECT * from DB;

                                             

                                            TEST2:

                                                 LOAD

                                                      FA_Location_Subcode,

                                                      FA_Clas

                                                 RESIDENT DATASOURCE;

                                             

                                            Drop Table DATASOURCE;