Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vpanchuda
Contributor III
Contributor III

Data inconsistancey in Qlik Script and SqL script

Hi Experts
 
 
I am stuck with the below problem since sometime.
 
When i query the as below script in Qlik view SCRIPT 1 , i get 38755 rows which is correct  ,but when i use the SCRIPT 2 i get 38343 rows 
 
Script 1 This script works fine but i need to do it in the other way as below because i need master table seperately and the joined table as island table
 
lOAD
T1-ITENOM,
T1-FA,
T1-WH,
T2-MMI
,
T2-RF,
T1-NO,
;
Sql Select
T1-ITENOM,T1-FA,T1-WH,T2-MMI,T2-RF,T1-NO
fROM BBDB.T1
INNER JOIN $BBDB.T2
 
ON
T1-ITENOM=T2-ITENOM
;
 
 
here in Script 2 I  have loaded the T1  and T2  and resident loading the 2 tabled to get the same data as in above script 
 
1-I have loaded the master table for both and it shows the complete rows.(checked)
2-I have added row number in script  as well,  as precaution so that data wont be missed.
3-I checked interchanging the table above and below also checked with left join as well .
 
Can you see anything wrong in the script , i am not sure if its problem wit the way join workes in qlik..:(
 
SCRIPT2
 
qualify *;
unqualify Itenom;
//(i have added the qualify and unqualify because i want this table as a island table it should not be linked with master tables)//

T3:
LOAD*,
T1.ITENOM As Itenom
RESIDENT T1;
 
//
//qualify *;
//unqualify Itenom;
 
inner  join
T3:
LOAD*,
T2.ITENOM As Itenom
RESIDENT T2;
Labels (1)
1 Solution

Accepted Solutions
marcus_sommer

The usual way to find such discrepancies is to apply recno() and rowno() to all loads and then to count and to compare all the records and of course to look which records are missing - often it's then quite obvious why they are missing. Before going this way it might be helpful to repeat  both approaches several times to check if the number of records is always the same or if the change randomly because of any locking of records.

Beside this there are multiple causes possible why both approaches differ, for example if any of these loads contain a DISTINCT statement. Another point are the values itself - if the join keys are pure numeric and the used number-system of your database and this from Qlik is different (it's quite probably the case) and/or if the keys contain some kind of NULL - which may be changed unnoticed in Qlik by using any NULL variables or NULL mappings - and which also may handled differently between the tools. Also the used driver may change the key-values and/or the number of records of any of these tables.

- Marcus

View solution in original post

4 Replies
JGMDataAnalysis
Creator III
Creator III

I assume that the type of data associated with the T1.ITEM and T2.ITEM fields is varchar. Based on this I suggest you try the following...

Lower(T1.ITENOM) AS Itenom
Lower(T2.ITENOM) AS Itenom
vpanchuda
Contributor III
Contributor III
Author

Tried that as well...my master tables i have given it as text(ITNO), i give the right format while loading the data itself so that i wont get such errors.

marcus_sommer

The usual way to find such discrepancies is to apply recno() and rowno() to all loads and then to count and to compare all the records and of course to look which records are missing - often it's then quite obvious why they are missing. Before going this way it might be helpful to repeat  both approaches several times to check if the number of records is always the same or if the change randomly because of any locking of records.

Beside this there are multiple causes possible why both approaches differ, for example if any of these loads contain a DISTINCT statement. Another point are the values itself - if the join keys are pure numeric and the used number-system of your database and this from Qlik is different (it's quite probably the case) and/or if the keys contain some kind of NULL - which may be changed unnoticed in Qlik by using any NULL variables or NULL mappings - and which also may handled differently between the tools. Also the used driver may change the key-values and/or the number of records of any of these tables.

- Marcus

Brett_Bleess
Former Employee
Former Employee

Vinayak, did Marcus' follow-up post help you get things sorted?  If so, be sure to give him credit by using Accept as Solution, but you can also post what you found using his suggestion and mark that post too, as I think in this case both would be needed, this way folks will know it helped you and if you can state what the actual issue was, they will have that piece too.  If you are still working on things, shout back with an update.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.