Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
AnkitMadhukar
Creator
Creator

Qlik Sense Scripting

Hi Everyone , 

Have you experienced a case when SQL executed on SSMS gives different records compared to when its run in Qlik View (using SQL:  keyword)

I get few extra records in Qlik for some reason and am trying to find the reason for this.

Sample script
SQL
Select  a.id , x , y , z FROM tablea a INNER JOIN tableb a ON a.id = b.id

 

The only difference is I am storing both the tables in separate QVDs and joining in Qlik 

Qlik Script

[TableA]

Load *;

SQL:
select * from TableA;

store [TableA] into(TableA.qvd)(qvd)

 

[TableB]


Load *;

SQL:
select * from TableB;

store [TableB] into(TableB.qvd)(qvd)

 

 

 

 

 

And in the Final App:

 

[Result Table]:

Load 
Id , 

from [TableA.qvd](qvd);

 

inner join
Load
ID,
B

from [TableB.qvd](qvd)

 



TL;DR : Can a same SQL query generate different results when executed in native environment(SSMS) vs when executed in QLIK

Thank You,
Ankit Madhukar

Labels (2)
2 Solutions

Accepted Solutions
Chanty4u
Champion III
Champion III

Hi,

Take new   qvw/qvf and do following and see .

instead of loading as a table in Qlik.

directly load sql table and check the count?

View solution in original post

AnkitMadhukar
Creator
Creator
Author

Hi  @Chanty4u  , 
I think I overlooked the fact that Qlik is case sensitive whereas SQL is not 🙂 
That was the cause of deviations in the count distinct case

Have made the required changes and will check the data load.
Thanks!

 

View solution in original post

7 Replies
AnkitMadhukar
Creator
Creator
Author

To add to this , 
I am getting minor deviations only.
Data matches as is for months till March , but after that I see these deviations.
Thanks!

Chanty4u
Champion III
Champion III

After loading into  Qvd . did you check individual table count?

 

AnkitMadhukar
Creator
Creator
Author

Hi @Chanty4u  , 
The number of records are different in QVD to SQL result (using same SQL query)

AnkitMadhukar
Creator
Creator
Author

For one of the table , I am getting the same number of records , but count distinct gives different result in SQL compared to QLIK.
I read how distinct is interpreted in QLIK in cases of joins and concatenates so I loaded that single table but still got the same result. The number of distinct count for one of the field in SQL does not match to that of Qlik.

Is there any thing I am missing here regarding this.

set analysis used  : Count( Distinct FieldName)

SQL : SELECT count(distinct FieldName) from tableA

Chanty4u
Champion III
Champion III

Hi,

Take new   qvw/qvf and do following and see .

instead of loading as a table in Qlik.

directly load sql table and check the count?

View solution in original post

AnkitMadhukar
Creator
Creator
Author

Hi  @Chanty4u  , 
I think I overlooked the fact that Qlik is case sensitive whereas SQL is not 🙂 
That was the cause of deviations in the count distinct case

Have made the required changes and will check the data load.
Thanks!

 

View solution in original post

Chanty4u
Champion III
Champion III

great  !  you are able to figure out the issue . please close this thread .