Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Read below article
www.qlikfix.com/2013/07/30/distinct-can-be-deceiving
Regards
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 ...
Hi,
Read below article
www.qlikfix.com/2013/07/30/distinct-can-be-deceiving
Regards
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.
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
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.
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.
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...??