17 Replies Latest reply: May 27, 2014 6:32 PM by Fadi El Khaja RSS

    Scripting a SQL sum() in QV Load

    Fadi El Khaja

      Hello folks!

      A QV newbie here!

      I want to load a GIANT dataset into QV and to make it very simple I want to use a SQL sum() in my statement to cut down on returned rows (since i'll sum them anyway).

       

      Basically I want to Load this:

      SELECT

      Field 1,  Field 2,  Field 3,  Field 4, sum(Field5) as Count5

      FROM

      Table1

      WHERE

      ...

      GROUP BY

      ...

      I figured this would be done by saying:

      Table:

      Load

      [Field 1] as [FirstField],  [Field 2] as [SecondField],  [Field 3] as [ThirdField],  [Field 4] [FourthField], Count5 as [CountOfFifthField]

       

      Now:

      The SQL on it's own works and gives me exactly what I need in a SQL edito

      The SQL without "sum(Field5) as Count5" works in QV.

       

      But once I add "sum(Field5) as Count5" to my SQL in QV, it complains that: Field not found <Count5>.

       

      How can I add that Sum() to my SQL so I do it in SQL rather than part of the Load statement itself (the bottleneck is pulling the data)

       

      Note: RDBMS is DB2, and the SQL above is pretty fake.. but pretty accurate structure wise (there's a LOT of joins in there )

        • Re: Scripting a SQL sum() in QV Load
          Massimo Grossi

          try to start with the simpler statement, no LOAD, no as

          does it works?

           

          SQL

          SELECT

          Field 1,  Field 2,  Field 3,  Field 4, sum(Field5)

          FROM

          Table1

          WHERE

          ...

          GROUP BY

          ...;

            • Re: Scripting a SQL sum() in QV Load
              Fadi El Khaja

              No load and no as works, sample data was loaded.

               

              I think my issue is that in the LOAD portion I can't "rename" sum(Field5), so i thought i'd give it a SQL alias. So that's why I added the SQL as

                • Re: Scripting a SQL sum() in QV Load

                  14                      To Connect a database using JDBC

                   

                  Program:

                  import java.sql.*;

                  import java.lang.*;

                  public class Db{

                    public static void main(String[] args) {

                    System.out.println("display result");

                    Connection con = null;

                    try{

                    Class.forName("com.mysql.jdbc.Driver");

                    con = DriverManager.getConnection("jdbc:mysql://localhost:3306/felistas","root","");

                  try{                                                                     

                    String sql = "select * from t1";

                    Statement st = con.createStatement();

                  ResultSet rs=st.executeQuery(sql);

                    System.out.println("result are!");

                    while(rs.next())

                  1. System.out.println(rs.getString("name")+"\t"+rs.getString("regno"));

                    con.close();

                    } catch (SQLException s){

                    System.out.println("SQL statement is not executed!");

                    }

                    }

                    catch (Exception e){

                    e.printStackTrace();

                    }

                    }

                  }

                  • Re: Scripting a SQL sum() in QV Load
                    Massimo Grossi

                    you can rename sum(Field5) in the LOAD portion

                     

                    [sum(Field5)] as ......

                      • Re: Scripting a SQL sum() in QV Load
                        Fadi El Khaja

                        I think that got me a bit closer! now it says that fieldisn't found:

                        Field not found - <sum(MCAQRYLIB.MPC_MBFINL.BWMEID05)>

                          • Re: Scripting a SQL sum() in QV Load
                            Massimo Grossi

                            if you load without LOAD, just sql select

                            and then CTRL-T (table viewer) I suppose you can see all field (upper/lower case)

                             

                            then, using these field names you add the LOAD section

                              • Re: Scripting a SQL sum() in QV Load
                                Fadi El Khaja

                                BINGO!!! you got it!

                                So the logic is, for "generated" fields I need to fetch the field name the database generates and then rename that in my LOAD!

                                THANK YOU!

                                  • Re: Scripting a SQL sum() in QV Load
                                    Marco Wedel

                                    can you please post the generated LOAD/SELECT for us to learn the changes?

                                     

                                    thanks

                                     

                                    regards

                                     

                                    Marco

                                      • Re: Scripting a SQL sum() in QV Load
                                        Fadi El Khaja

                                        So this will differ in one case to the other. But basically the method was:

                                        1. Run the SQL in QVD alone without LOAD
                                        2. CTRL-T and Locate the generated field name
                                        3. Add the generated name (and any other names) to your LOAD.

                                         

                                        So step 1:

                                        Run the script:

                                                SQL SELECT

                                        Field1,  Field2,  Field3,  "Field4, sum(Field5)

                                        FROM Table1

                                        WHERE  Field1 = 'test query'

                                        GROUP BY Field1,  Field2, Field3,  Field4 ;

                                         

                                        Step 2:

                                        CTRL-T to find what the Database returned as a field name for "sum(Field5)"

                                         

                                        Step 3:

                                        LOAD

                                        [Field1] as [FirstField],  [Field2] as [SecondField],  [Field3] as [ThirdField],  [Field4] as [FourthField], 00012 as [SumOfFifthField];

                                                SQL SELECT

                                        "Field 1",  "Field 2",  "Field 3",  "Field 4", sum("Field5") as Count5

                                        FROM Table1

                                        WHERE  Field1 = 'test query'

                                        GROUP BY Field1,  Field2, Field3,  Field4;

                                         

                                        So in this case 00012 was given by the database to the sum() function.

                                         

                                        NB: The queries and loads above are written by hand (not my actuals) because my actual is WAY too complex to point out the exact solve (50 lines of SQL), but this should tell you the story.

                                         

                                        I actually replicated that logic for a count() I had in another tab of my script, and works there too! excellent thought process by maxgro (grazie mille)

                          • Re: Scripting a SQL sum() in QV Load
                            Henric Cronström

                            I am not sure I understand... Are you saying that

                             

                            ODBC CONNECT TO <DB2_Data_Source> ;

                            SQL SELECT

                            "Field 1",  "Field 2",  "Field 3",  "Field 4", sum("Field5") as Count5

                            FROM Table1

                            WHERE   ...

                            GROUP BY "Field 1",  "Field 2",  "Field 3",  "Field 4" ;

                             

                            doesn't work in QlikView? If the connect string is OK and the fields exist, I see no reason why it shouldn't.

                             

                            HIC

                              • Re: Scripting a SQL sum() in QV Load
                                Fadi El Khaja

                                My Mistake, I would've been more clear.

                                 

                                I'm saying this works:

                                ODBC CONNECT TO <DB2_Data_Source> ;

                                SQL SELECT

                                "Field 1",  "Field 2",  "Field 3",  "Field 4", sum("Field5") as Count5

                                FROM Table1

                                WHERE   ...

                                GROUP BY "Field 1",  "Field 2",  "Field 3",  "Field 4" ;

                                 

                                But this doesn't:

                                ODBC CONNECT TO <DB2_Data_Source> ;

                                Table:

                                Load

                                [Field 1] as [FirstField],  [Field 2] as [SecondField],  [Field 3] as [ThirdField],  [Field 4] [FourthField], Count5 as [CountOfFifthField]

                                 

                                         SQL SELECT

                                "Field 1",  "Field 2",  "Field 3",  "Field 4", sum("Field5") as Count5

                                FROM Table1

                                WHERE   ...

                                GROUP BY "Field 1",  "Field 2",  "Field 3",  "Field 4" ;