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: 
vlad_komarov
Partner - Specialist III
Partner - Specialist III

UNION instead of UNION ALL (Concatenate in QV terms)

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

1 Solution

Accepted Solutions
Anonymous
Not applicable

Table1:

LOAD

KeyField,

...

FROM source1;

CONCATENATE (Table1) LOAD

KeyField,

...

FROM source2

WHERE not exists(KeyField);

View solution in original post

7 Replies
petter
Partner - Champion III
Partner - Champion III

If you LOAD your data with DISTINCT you should achieve what you want ... I think

Anonymous
Not applicable

Table1:

LOAD

KeyField,

...

FROM source1;

CONCATENATE (Table1) LOAD

KeyField,

...

FROM source2

WHERE not exists(KeyField);

b_garside
Partner - Specialist
Partner - Specialist

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:

https://community.qlik.com/message/249169#249169

vlad_komarov
Partner - Specialist III
Partner - Specialist III
Author

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

vlad_komarov
Partner - Specialist III
Partner - Specialist III
Author

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

vlad_komarov
Partner - Specialist III
Partner - Specialist III
Author

Petter,

Using "distinct" alone did not solve the problem. But I've used it in my code too.

Thanks!

Vladimir

vlad_komarov
Partner - Specialist III
Partner - Specialist III
Author


Samle code: