topic Re: Table a minus Table b in QlikView

Table a minus Table b

Anonymous — Fri, 05 Dec 2014 17:18:23 GMT

Hello,

I have two tables.

TABLE A

LOAD CODE,

DATE

FROM TABLE A;

TABLE B

LOAD CODE,

DATE

FROM TABLE B;

TABLE A

CODE DATE

1 201401

2 201402

3 201403

TABLE B

CODE DATE

1 201401

I want to make Table A minus(-) Table B and the result will be.

TABLE AB

CODE DATE

2 201402

3 201403

How to do this?

Thanks.

Att.

Re: Table a minus Table b

its_anandrjs — Fri, 05 Dec 2014 17:27:55 GMT

Hi,

Then you have to try this ways

TB:

LOAD CODE as CODE2, DATE2;

LOAD * INLINE [

CODE, DATE2

1, 201401 ];

TA:

LOAD CODE, DATE;

LOAD * INLINE [

CODE, DATE

1, 201401

2, 201402

3, 201403 ] Where not Exists(CODE2,CODE);

DROP Table TB;

Regards

Anand

Re: Table a minus Table b

Anonymous — Fri, 05 Dec 2014 17:49:43 GMT

Anand,

This table was an example, the real table has a lot of values, I cant do this...

How could I do?

Help me please.

Re: Table a minus Table b

its_anandrjs — Fri, 05 Dec 2014 17:55:07 GMT

Hi,

Yes with this simple example code you can do this by this example you can apply for your example also need two tables and the Key field like here i use CODE. And there is also another way of doing the same see the example load below

Script:-

TB:

LOAD CODE, DATE;

LOAD * INLINE [

CODE, DATE

1, 201401

];

NoConcatenate

TA:

LOAD CODE, DATE;

LOAD * INLINE [

CODE, DATE

1, 201401

2, 201402

3, 201403

] Where not Exists(CODE,CODE);

DROP Table TB;

You get only this records

2, 201402

3, 201403

Regards

Anand

Re: Table a minus Table b

MarcoWedel — Sat, 06 Dec 2014 00:36:50 GMT

http://community.qlik.com/message/674541#674541

hope this helps

regards

Marco

Re: Re: Table a minus Table b

MarcoWedel — Sat, 06 Dec 2014 10:43:16 GMT

Hi,

one solution could be:

Table1:
LOAD @1 as ID,
    'Table1' as table
FROM [http://community.qlik.com/thread/144882] (html, codepage is 1252, no labels, table is @1, filters(Remove(Row, Pos(Top, 1)),Remove(Row, Pos(Top, 1))))
Where Len(@1);

NoConcatenate

Table2:
LOAD @2 as ID,
    'Table2' as table
FROM [http://community.qlik.com/thread/144882] (html, codepage is 1252, no labels, table is @1, filters(Remove(Row, Pos(Top, 1)),Remove(Row, Pos(Top, 1))))
Where Len(@2);

tabTemp:
LOAD ID as ID2
Resident Table2;

NoConcatenate

Table3:
LOAD ID,
    'Table3' as table
Resident Table1
Where not Exists(ID2, ID);

Drop Table tabTemp;

hope this helps

regards

Marco

Re: Table a minus Table b

alkesh_sharma — Sat, 06 Dec 2014 10:58:10 GMT

Try this:

LOAD Code as Key;

from Table A;

Load Code,

Date,

From Table B where Not Exists (A, Key)

Regard,

Alkesh