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.

hkg_qlik
Contributor III

Union

Hi,

I have two tables with all the same field names:

Table A has 28000 records (QVD)

Table B has 100,000 records (SQL Server Table)

I am loading Table A first and want to combine Table B with Table A.

While combining the TABLE A and TABLE B i want to keep all records from TABLE A but exclude the same records from TABLE B based on MEMBERID.

Usually we use union in SQL.. How can we achieve the simliar result while loading in QLikView.

Thanks,

H

1 Solution

Accepted Solutions
Not applicable

Re: Union

Hello, you can use the EXISTS function.

First you read the first table (TABLE_A).

Creates an additional field identifier for this table: (MEMBERID_A).

Then makes the union between tables using concatenate.

In the filter of your second table, just add the following filter:

WHERE NOT EXISTS (MEMBERID_A, MEMBERID)

Ex.:

TABLE_A:

LOAD

MEMBERID AS MEMBERID_A,

*

FROM ....

TABLE_B:

LOAD

*

FROM ....

RESULT_TABLE:

LOAD *

// DON't USE THE FIELD CREATED (MEMBERID_A)

...

RESIDENT TABLE_A;

CONCATENATE

LOAD *

RESIDENT TABLE_B

WHERE NOT EXISTS(MEMBERID_A, MEMBERID);

3 Replies
SunilChauhan
Esteemed Contributor

Re: Union

you can use

Table A:

Load

.......

Join(Table A)

Table B

Load

....

hope this helps

hkg_qlik
Contributor III

Re: Union

If i use join I see duplicate records for members present in Table A.

-H

Not applicable

Re: Union

Hello, you can use the EXISTS function.

First you read the first table (TABLE_A).

Creates an additional field identifier for this table: (MEMBERID_A).

Then makes the union between tables using concatenate.

In the filter of your second table, just add the following filter:

WHERE NOT EXISTS (MEMBERID_A, MEMBERID)

Ex.:

TABLE_A:

LOAD

MEMBERID AS MEMBERID_A,

*

FROM ....

TABLE_B:

LOAD

*

FROM ....

RESULT_TABLE:

LOAD *

// DON't USE THE FIELD CREATED (MEMBERID_A)

...

RESIDENT TABLE_A;

CONCATENATE

LOAD *

RESIDENT TABLE_B

WHERE NOT EXISTS(MEMBERID_A, MEMBERID);