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: 
Not applicable

Distinct Inner Join - Strange Results.

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

1 Solution

Accepted Solutions
PrashantSangle

Hi,

Read below article

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

7 Replies
Not applicable
Author

Hi,

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

Main:

load * Inline [

field

A

B

C

A

;

Inner join

Load * Inline [

field

A

C

];

will return 3 line record...

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

Main:

load * Inline [

field

A

B

C

A

];

inner  join


Load Distinct * Inline [

field

A

C

];


will return 2 line record ...

PrashantSangle

Hi,

Read below article

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
danieloberbilli
Specialist II
Specialist II

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.

ziadm
Specialist
Specialist

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

Not applicable
Author

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.

Not applicable
Author

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.

dhasharadh
Creator III
Creator III

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...??