topic Re: Distinct Inner Join - Strange Results. in QlikView

Distinct Inner Join - Strange Results.

Wed, 22 Apr 2015 05:34:40 GMT

Hi,

Found a bizarre issue and can't seem to get my head around what would cause this problem.

I am trying to do a Distinct Inner join on a very large data set to remove data.

Usage:
LOAD 
     UserID,
     Unit,
     UsageType
FROM
[..\3.QVD\DataModels\Usage\2015-*-*.qvd]
(qvd);

Inner Join

LOAD
  Distinct
  UserID
FROM
[..\3.QVD\DataModels\Customer.qvd]
(qvd);

This returns a total of 1600 results which is incorrect. If I do:

Customer:
LOAD
  Distinct
  UserID
FROM
[..\3.QVD\DataModels\Customer.qvd]
(qvd);

Usage:
LOAD 
     UserID,
     Unit,
     UsageType
FROM
[..\3.QVD\DataModels\Usage\2015-*-*.qvd]
(qvd);

Inner Join

Load
     UserID
Resident Customer;

Drop Table Customer;

This returns 35,502 which is correct. I don't understand why the two results should differ. I have written both methods and the same list of values for UserID are returned. If create an INLINE * FROM [ UserID etc ] this also provides the correct results.

Further testing:

Usage:
LOAD 
     UserID,
     Unit,
     UsageType
FROM
[..\3.QVD\DataModels\Usage\2015-*-*.qvd]
(qvd);

Inner Join

LOAD
    UserID,
     Count(UserID) As Counter
FROM
[..\3.QVD\DataModels\Customer.qvd]
(qvd)
Group By UserID;

Drop Field Counter;

This also provides the correct as results above.

Any ideas on what could be causing this issue?

-Chris

Re: Distinct Inner Join

Wed, 22 Apr 2015 05:55:11 GMT

Hi,

See below o/p if your data is like below inner join perform results differently,

Main:

load * Inline [

field

;

Inner join

Load * Inline [

field

];

will return 3 line record...

-----------------------------

Main:

load * Inline [

field

];

inner join

Load Distinct * Inline [

field

];

will return 2 line record ...

Re: Distinct Inner Join - Strange Results.

PrashantSangle — Wed, 22 Apr 2015 05:58:53 GMT

Hi,

Read below article

www.qlikfix.com/2013/07/30/distinct-can-be-deceiving

Regards

Re: Distinct Inner Join - Strange Results.

danieloberbilli — Wed, 22 Apr 2015 06:02:41 GMT

Your first example has a 'Join distinct' which results in distinct values only.

Although in your next examples your join table is based on a previously loaded (distinct) table - that doesnt cause the same effect as you dont write a 'join distinct' but join only.

Re: Distinct Inner Join - Strange Results.

ziadm — Wed, 22 Apr 2015 06:10:52 GMT

HI Inner Join will give you the result of Dataset on the left intersected with Dataset on the right. Rows that does not exist in any of the Data sets will be omitted Only rows that exists in both set having the same UserID will be resulted from the join. regards

Re: Distinct Inner Join

Wed, 22 Apr 2015 06:15:50 GMT

Ah many thanks....

For some reason the overall logic seems a little backwards and painful just because it provides some strange results.. Now I have to change a few documents to reflect different logic. Oops.

Re: Distinct Inner Join - Strange Results.

Wed, 22 Apr 2015 08:10:09 GMT

For anyone interested, if I add a unique key to each record on Usage prior to doing the distinct inner join it provides the correct number of records.

If I continue and then drop the key field at the end of the script, it results return back to the incorrect number. Very bizarre behavior.

Re: Distinct Inner Join - Strange Results.

dhasharadh — Mon, 22 Jul 2019 10:01:46 GMT

Thanks for this, have been struggling from couple of days on the same issue.

So we should not use distinct with inner join ???

or what is the conclusion of this thread, am stuck into same situation where we were migrating to Qv 12 and values on the Qv 12 are not matching by with Qv 11 even though the script the and database that they both are hitting also same.

we got to know that distinct with Joins behave differently in the Qlikview 12 and Qlkview 11..

is it true...??