Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Scripting a SQL sum() in QV Load

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 )

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: Scripting a SQL sum() in QV Load

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

17 Replies
MVP
MVP

Re: Scripting a SQL sum() in QV Load

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

...;

Employee
Employee

Re: Scripting a SQL sum() in QV Load

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

Not applicable

Re: Scripting a SQL sum() in QV Load

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

Not applicable

Re: Scripting a SQL sum() in QV Load

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

Not applicable

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();

  }

  }

}

eduardo_sommer
Valued Contributor

Re: Scripting a SQL sum() in QV Load

Maybe its a typo, but an as is missing between [Field4] and [FourthField]

Eduardo

Not applicable

Re: Scripting a SQL sum() in QV Load

no true

Not applicable

Re: Scripting a SQL sum() in QV Load

use jdbc

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

not sql

MVP
MVP

Re: Scripting a SQL sum() in QV Load

you can rename sum(Field5) in the LOAD portion

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

Community Browser