Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: Distinct Inner Join - Strange Results.

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.
6 Replies
Not applicable

Re: Distinct Inner Join

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

Re: Distinct Inner Join - Strange Results.

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.
danieloberbilli
Valued Contributor II

Re: Distinct Inner Join - Strange Results.

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
Valued Contributor

Re: Distinct Inner Join - Strange Results.

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

Re: Distinct Inner Join

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

Re: Distinct Inner Join - Strange Results.

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.