Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I was searching the forum for a simple solution: I need to "unionize" two data tables, but with duplicate records removed.
There are few articles about using "CONCATENATE" in QV instead of SQL UNION, but as far I understand the CONCATENATE works as UNION ALL, which is not a good idea in my case.
I am searching for a simple solution for actual UNION functionality (I would need duplicate records removed in my result table). Any suggestions for a simple solution?
Best regards,
Vladimir
Table1:
LOAD
KeyField,
...
FROM source1;
CONCATENATE (Table1) LOAD
KeyField,
...
FROM source2
WHERE not exists(KeyField);
If you LOAD your data with DISTINCT you should achieve what you want ... I think
Table1:
LOAD
KeyField,
...
FROM source1;
CONCATENATE (Table1) LOAD
KeyField,
...
FROM source2
WHERE not exists(KeyField);
You can create a composite Key Field&Field2... that will help you determine what is a duplicate or not.
Then you Count them in a Grouped table and look for any that count greater than >1 which indicates the key has been duplicated.
Granted this is more complicated then Micheal's solution which should be tried first.
Once the key is counted then Join it back to the Concatenated table on third step use Where CountKey=1
Examples:
Michael,
I've combined your suggestion (using "WHERE not exists(KeyField);") with Brian's (using composite key) and I've got the solution I was looking for.
Thank you for suggestion!
Vladimir
Brian,
I've used your suggestion (using composite key) along with Michael's (using "WHERE not exists(KeyField);") and it looks like a solution for my problem.
Thank you for suggestion!
Vladimir
Petter,
Using "distinct" alone did not solve the problem. But I've used it in my code too.
Thanks!
Vladimir
Samle code: