topic Re: Data inconsistancey in Qlik Script and SqL script in QlikView

Data inconsistancey in Qlik Script and SqL script

vpanchuda — Sat, 16 Nov 2024 19:40:37 GMT

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

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;

Re: Data inconsistancey in Qlik Script and SqL script

JGMDataAnalysis — Thu, 14 Nov 2019 12:10:47 GMT

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

Re: Data inconsistancey in Qlik Script and SqL script

vpanchuda — Thu, 14 Nov 2019 12:45:58 GMT

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.

Re: Data inconsistancey in Qlik Script and SqL script

marcus_sommer — Thu, 14 Nov 2019 15:12:31 GMT

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

Re: Data inconsistancey in Qlik Script and SqL script

Brett_Bleess — Tue, 19 Nov 2019 13:59:55 GMT

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