Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
vpanchuda
New Contributor II

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;
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Data inconsistancey in Qlik Script and SqL script

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
Highlighted
JGMDataAnalysis
Contributor II

Re: Data inconsistancey in Qlik Script and SqL script

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
Highlighted
vpanchuda
New Contributor II

Re: Data inconsistancey in Qlik Script and SqL script

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.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Data inconsistancey in Qlik Script and SqL script

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

Highlighted
Digital Support
Digital Support

Re: Data inconsistancey in Qlik Script and SqL script

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 don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.