Group by and Union in SQL load statement

[jarrad_murray]

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.

[sunny_talwar]

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

;

[jarrad_murray]

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.

[sunny_talwar]

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

;

[jarrad_murray]

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.

[sunny_talwar]

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

;

[jarrad_murray]

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.

[sunny_talwar]

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

[jarrad_murray]

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.

[sunny_talwar]

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