Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

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
mov
Esteemed Contributor III

Re: UNION instead of UNION ALL (Concatenate in QV terms)

Table1:

LOAD

KeyField,

...

FROM source1;

CONCATENATE (Table1) LOAD

KeyField,

...

FROM source2

WHERE not exists(KeyField);

View solution in original post

7 Replies
MVP
MVP

Re: UNION instead of UNION ALL (Concatenate in QV terms)

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

mov
Esteemed Contributor III

Re: UNION instead of UNION ALL (Concatenate in QV terms)

Table1:

LOAD

KeyField,

...

FROM source1;

CONCATENATE (Table1) LOAD

KeyField,

...

FROM source2

WHERE not exists(KeyField);

View solution in original post

Partner
Partner

Re: UNION instead of UNION ALL (Concatenate in QV terms)

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

Partner
Partner

Re: UNION instead of UNION ALL (Concatenate in QV terms)

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

Partner
Partner

Re: UNION instead of UNION ALL (Concatenate in QV terms)

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

Partner
Partner

Re: UNION instead of UNION ALL (Concatenate in QV terms)

Petter,

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

Thanks!

Vladimir

Partner
Partner

Re: UNION instead of UNION ALL (Concatenate in QV terms)


Samle code: