topic Re: Non Matching records with NOT EXIST in QlikView

Non Matching records with NOT EXIST

Anonymous — Tue, 15 Oct 2013 04:54:23 GMT

Hi Friends,

I am trying to pull non-matched records from below table1, & table2.

Expected o/p

Non_Matched

F1, F3

3, C

Table1:

LOAD * INLINE [
F1, F2
1, A
5, E
6, F
];

Table2:
LOAD * INLINE [
F1, F3
1, A
3, C
6, F
];

Non_Matched:
LOAD
F1,
F3
Resident Table2
Where not Exists(Table1,F1);

But above script not producing any o/p, Can one of you let me Know how to compare table2 records with table1 and then pull only non-matched record of table2.

Thanks,

Ramesh.

Re: Non Matching records with NOT EXIST

sudeepkm — Tue, 15 Oct 2013 05:04:31 GMT

can you pls try this:

Table1:

LOAD * INLINE [

F1, F2

1, A

5, E

6, F

];

//Table2:

Right join LOAD * INLINE [

F1, F3

1, A

3, C

6, F

] where not Exists(F1);

Do you want your main tables to be present and the result table be another table?

Re: Non Matching records with NOT EXIST

Anonymous — Tue, 15 Oct 2013 05:08:15 GMT

Hi Sudheep,

Thanks for your response.

Yes i am looking Non_matched records on sperate table and also i wanted to keep orginal input tables as it is.

Thanks,

Ramesh.

Re: Non Matching records with NOT EXIST

CELAMBARASAN — Tue, 15 Oct 2013 05:25:21 GMT

Possibly you could use something like this

Table1Temp:

LOAD * INLINE [

F1, F2

1, A

5, E

6, F

];

Table2:

LOAD * INLINE [

F1, F3

1, A

3, C

6, F

];

Outer Join(Table1Temp)

LOAD

F1,

Resident Table2;

Qualify *;

Non_Matched:

LOAD

F1,

Resident Table1Temp

Where IsNull(F2);

UNQUALIFY *;

Table1:

LOAD

F1,

Resident Table1Temp

Where Not IsNull(F2);

Drop Table Table1Temp;

Re: Non Matching records with NOT EXIST

sibin_jacob — Tue, 15 Oct 2013 05:29:19 GMT

Hi Ramesh,

Please try the below script and let me know if you have any concerns/questions.

LOAD * INLINE [

F1, F2

1, A

5, E

6, F

];

Table2:

LOAD * INLINE [

F1 , F3

1, A

3, C

6, F

];

Table3:

LOAD

F1 ,

F2 ,

F1 as F4

Resident Table1;

join

LOAD

F1 ,

F3

Resident Table2;

Non_Matched:
LOAD
F1 as F4,

F3 as F5

Resident Table3

Where not Exists(F4)
;
Drop tables Table3;
RENAME Field F4 to F1;

RENAME Field F5 to F3;

Thanks,

Sibin Jacob.C

Re: Non Matching records with NOT EXIST

Anonymous — Tue, 15 Oct 2013 05:38:31 GMT

Thanks its worked.

Re: Non Matching records with NOT EXIST

Anonymous — Tue, 15 Oct 2013 05:39:08 GMT

Thanks this one also worked.

Re: Non Matching records with NOT EXIST

vivek_niti — Tue, 15 Oct 2013 06:06:28 GMT

Hi,

Actually you dont need to do an Outer Join for the result.

Table1:

LOAD * INLINE [
F1, F2
1, A
5, E
6, F
];

Table2:
LOAD * INLINE [
F11, F3
1, A
3, C
6, F
];

Non_Matched:

NOCONCATENATE
LOAD
F1,
F3
Resident Table2
Where not Exists(F11,F1);

DROP TABLE Table2;

This will also work.

Re: Non Matching records with NOT EXIST

Anonymous — Tue, 15 Oct 2013 06:16:46 GMT

Hi Vivek, Thanks for your suggestion.

Can you attach your app, I tried above code and it did not work for me where it is not producing any records in Non_matching table.

Re: Re: Non Matching records with NOT EXIST

vivek_niti — Tue, 15 Oct 2013 06:53:07 GMT

Hi,

Please find the attached file.

Re: Non Matching records with NOT EXIST

Tue, 15 Oct 2013 09:32:52 GMT

try this

Table1:

LOAD * INLINE [

F1, F2

1, A

5, E

6, F

];

Table2:

LOAD * INLINE [

F1, F3

1, A

3, C

6, F

];

data1:

LOAD

F1 as F1_new,

F2 as F2_new

Resident Table1;

join

data2:

LOAD

F1 as F1_new,

F3 as F3_new

Resident Table2;

output:

LOAD

F1_new as F1_new1,

F3_new as F3_new1

Resident data1

Where F1_new=3;

DROP Table data1;