topic Extract Not Matched Records in QlikView

Extract Not Matched Records

nihhalmca — Tue, 19 Aug 2014 13:22:43 GMT

Hi All,

I want to extract not matched records from 2 table based on common column.

For Example:

Table 1

ID, Amount

1, 100

2, 200

Table 2

ID, Amount

1, 100

2, 200

4, 400

Result Should be

Table

ID, Amount

4, 400

Regards,

NiHhal.

Re: Extract Not Matched Records

JonnyPoole — Tue, 19 Aug 2014 13:31:53 GMT

Is the purpose to create 1 table without duplication ? Or are you trying to create 2 separate tables with a common column field whose data never matches ?

Re: Extract Not Matched Records

nihhalmca — Tue, 19 Aug 2014 13:37:03 GMT

For Example:

Table 1

ID, Amount

1, 100

2, 200

Table 2

ID, Amount

1, 100

2, 200

4, 400

Result Should be

Table

ID, Amount

4, 400

Re: Extract Not Matched Records

martynlloyd — Tue, 19 Aug 2014 13:48:24 GMT

Try

Table1:

ID as Key1

...

TableX:

LOAD

ID,

Amount

From ...

Where Not Exists(Key1, ID)

;

Re: Extract Not Matched Records

JonnyPoole — Tue, 19 Aug 2014 13:51:57 GMT

i added a 2nd reference to the ID fields which i dropped later .

Table1:

LOAD * INLINE [

ID, ID1, Amount

1, 1, 100

2, 2, 200

];

Outer join (Table1)

LOAD * INLINE [

ID,ID2, Amount

1, 1, 100

2, 2, 200

4, 4, 400

];

FinalTable:

NoConcatenate

Load *

Resident Table1

where ID1<>ID2;

drop table Table1;

drop Fields ID1,ID2;

Re: Extract Not Matched Records

nihhalmca — Wed, 20 Aug 2014 10:54:09 GMT

Thanks for reply Martyn,

I tried you example, it is giving 2 table.

I am expecting one table that should has not matched records.

Regards,

Nihhal.

Re: Re: Extract Not Matched Records

tresB — Wed, 20 Aug 2014 10:59:35 GMT

See attached sample qvw.

Re: Extract Not Matched Records

ashfaq_haseeb — Wed, 20 Aug 2014 11:19:20 GMT

Hi,

Try below

Table1:

Load * Inline [

ID,Amount

1,100

2,200];

Table2:

Load ID as ID1 ,Amount

where not Exists(ID, ID);

load * Inline [

ID, Amount

1,100

2,200

4,400

]

;

drop table Table1;

Regards

ASHFAQ