topic Re: Group by and Union in SQL load statement in QlikView

Group by and Union in SQL load statement

jarrad_murray — Fri, 02 Jun 2017 10:43:30 GMT

Hi All,

Sorry if I am posting this in the wrong place.

I am trying to load two sets of data using SQL into a Qlik dashboard and seem to be having some difficulties.

I have loaded the first data set using the following syntax

Qlik Table 1:

SQL

Select

,Column 1

,Column 2

,Count(*) as 'Column 3

from

Table 1

Group by

,Column 1

,Column 2

UNION ALL

Select

,Column 1

,Column 2

,Count(*) as 'Column 3

from

Table 2

Group by

,Column 1

,Column 2

;

This works fine and creates a table in Qlik as expected. Performing the same action for the second table causes Qlik to just ignore the load statement and not action anything. The statement I am using is exactly the same just creating table 2 and getting the source data from a different set of tables.

I have run the query code independently in SQL and the query works fine. Confusingly, I can get the table to create itself by removing the group by aspect and running the SQL separately i.e.

Qlik Table 2:

SQL

Select

,Column 1

,Column 2

,1 as 'Column 3

from

Table 3

;

Qlik Table 2:

SQL

Select

,Column 1

,Column 2

,1 as 'Column 3

from

Table 4

;

Does anyone have any idea as to what might be going on here and how I can revert my SQL query back to a group and union as I am dealing with hundreds of thousands of rows of data and am going to make the Qlik application massive by the time I have finished.

Re: Group by and Union in SQL load statement

sunny_talwar — Fri, 02 Jun 2017 10:46:48 GMT

Try to add NoConcatenate before your QlikTable2...

Qlik Table 2:

NoConcatenate

SQL

Select

,Column 1

,Column 2

,1 as 'Column 3

from

Table 3

;

Table 1:

SQL

Select

,Column 1

,Column 2

,1 as 'Column 3

from

Table 4

;

Re: Group by and Union in SQL load statement

jarrad_murray — Fri, 02 Jun 2017 10:58:05 GMT

Hi Sunny,

Sorry I think I have confused things, I am trying to get the tables to concatenate without having to run the individual script twice. The script I posted has an error, the script should have read

Qlik Table 2:

SQL

Select

,Column 1

,Column 2

,1 as 'Column 3

from

Table 3

;

Qlik Table 2:

SQL

Select

,Column 1

,Column 2

,1 as 'Column 3

from

Table 4

;

I am trying to perform the same action in SQL using a group by on the data and Union all to bring the two tables together.

Sorry for the confusion.

Re: Group by and Union in SQL load statement

sunny_talwar — Fri, 02 Jun 2017 11:01:28 GMT

What if you do a concatenation by implicitly calling out concatenate() function

Qlik Table 2:

SQL

Select

,Column 1

,Column 2

,1 as 'Column 3

from

Table 3

;

Concatenate (Qlik Table 2)

SQL

Select

,Column 1

,Column 2

,1 as 'Column 3

from

Table 4

;

Re: Group by and Union in SQL load statement

jarrad_murray — Fri, 02 Jun 2017 11:07:39 GMT

The concatenation of the table in the individual load statements isn't the problem. By running the above code I get both data sets from Table 3 and Table 4 into the Qlik Table 2 with no issues.

the problem that I am having is when I run the SQL statement

Qlik Table 2:

SQL

Select

,Column 1

,Column 2

,Count(*) as 'Column 3

from

Table 3

Group by

,Column 1

,Column 2

UNION ALL

Select

,Column 1

,Column 2

,Count(*) as 'Column 3

from

Table 4

Group by

,Column 1

,Column 2

;

The table doesn't load, the only way I can get data from tables 3 & 4 is to remove the group by (which is pushing my record count into the hundreds of thousands) and to run each select independently so Qlik performs the concatenation.

Sorry if I'm not explaining this very well.

Re: Group by and Union in SQL load statement

sunny_talwar — Fri, 02 Jun 2017 11:10:01 GMT

Gotchya.... so the SQL union isn't working for you... have you tried to run this query outside of Qlik? May be in Toad to see if this runs

Select

,Column 1

,Column 2

,Count(*) as 'Column 3

from

Table 3

Group by

,Column 1

,Column 2

UNION ALL

Select

,Column 1

,Column 2

,Count(*) as 'Column 3

from

Table 4

Group by

,Column 1

,Column 2

;

Re: Group by and Union in SQL load statement

jarrad_murray — Fri, 02 Jun 2017 11:25:43 GMT

Yeah, running the query in SQL works fine. No errors and everything grouping as expected.

It just doesn't work when I try and load it into Qlik. If I remove the union or the group by it doesn't load. Only by removing both the union and the group by does Qlik accept it and load the data.

It's very odd.

Re: Group by and Union in SQL load statement

sunny_talwar — Fri, 02 Jun 2017 11:27:47 GMT

Do you have OLE DB connection to your database through QlikView?

Re: Group by and Union in SQL load statement

jarrad_murray — Fri, 02 Jun 2017 11:30:13 GMT

Yeah.

I am loading two different sets of data from the same database. The first set loads fine (using union and group by) the second set will only load by getting qlik to concatenate the loads.

Re: Group by and Union in SQL load statement

sunny_talwar — Fri, 02 Jun 2017 11:30:57 GMT

OLE DB connection might be the issue, can you try with ODBC Connection to your database?

Re: Group by and Union in SQL load statement

jarrad_murray — Fri, 02 Jun 2017 11:35:41 GMT

Wouldn't the issue of the connection affect the first query too not just the second?

Re: Group by and Union in SQL load statement

sunny_talwar — Fri, 02 Jun 2017 11:38:04 GMT

I don't understand ODBC or OLE DB connections well enough to answer this, but I have seen some weird things happening using OLE DB connections. That is why I suggested trying out the ODBC connection to see if that resolves your issue.

Note: I don't know if it will solve your issue or not, but I think its worth giving a shot

Re: Group by and Union in SQL load statement

jarrad_murray — Fri, 02 Jun 2017 12:02:15 GMT

I'm not managing to get an ODBC connection working. Will continue to try and let you know the outcome. Thank you for your help so far.