topic Re: Scripting a SQL sum() in QV Load in QlikView

Scripting a SQL sum() in QV Load

Tue, 27 May 2014 19:07:54 GMT

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

maxgro — Tue, 27 May 2014 19:27:09 GMT

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

hic — Tue, 27 May 2014 19:33:16 GMT

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

Tue, 27 May 2014 19:37:20 GMT

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

Re: Scripting a SQL sum() in QV Load

Tue, 27 May 2014 19:39:42 GMT

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

Tue, 27 May 2014 19:43:40 GMT

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

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

eduardo_sommer — Tue, 27 May 2014 19:44:06 GMT

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

Eduardo

Re: Scripting a SQL sum() in QV Load

Tue, 27 May 2014 19:45:44 GMT

no true

Re: Scripting a SQL sum() in QV Load

Tue, 27 May 2014 19:46:25 GMT

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

Re: Scripting a SQL sum() in QV Load

maxgro — Tue, 27 May 2014 19:48:00 GMT

you can rename sum(Field5) in the LOAD portion

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

Re: Scripting a SQL sum() in QV Load

Tue, 27 May 2014 19:49:04 GMT

yes, an as is there, it's a typo in here (i manually typed the example)

Re: Scripting a SQL sum() in QV Load

its_anandrjs — Tue, 27 May 2014 19:49:45 GMT

It seems you put count statement twice for the load statement use it only once and try

ODBC CONNECT TO <DB2_Data_Source> ;

Table:

Load

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

SQL SELECT

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

FROM Table1

WHERE ...

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

Re: Scripting a SQL sum() in QV Load

Tue, 27 May 2014 19:54:11 GMT

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

maxgro — Tue, 27 May 2014 20:00:47 GMT

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: Re: Scripting a SQL sum() in QV Load

MarcoWedel — Tue, 27 May 2014 21:27:30 GMT

Isn't there a semicolon missing at the end of the preceding load?

Load

[Field 1] as [FirstField], [Field 2] as [SecondField], [Field 3] as [ThirdField], [Field 4] as [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" ;

regards

Marco

Re: Scripting a SQL sum() in QV Load

Tue, 27 May 2014 21:45:40 GMT

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

MarcoWedel — Tue, 27 May 2014 21:51:39 GMT

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

Tue, 27 May 2014 22:32:42 GMT

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

Run the SQL in QVD alone without LOAD
CTRL-T and Locate the generated field name
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 Massimo Grossi (grazie mille)