topic Re: Union in QlikView

Union

hkg_qlik — Thu, 07 Feb 2013 15:32:35 GMT

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,

Re: Union

SunilChauhan — Thu, 07 Feb 2013 15:40:17 GMT

you can use

Table A:

Load

.......

Join(Table A)

Table B

Load

....

hope this helps

Re: Union

hkg_qlik — Thu, 07 Feb 2013 15:47:17 GMT

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

-H

Re: Union

Thu, 07 Feb 2013 15:58:24 GMT

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);