Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jaswantc
Contributor II

Non Matching records with NOT EXIST

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.

Tags (1)
1 Solution

Accepted Solutions

Re: Non Matching records with NOT EXIST

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,

F3

Resident Table2;

Qualify *;

Non_Matched:

LOAD

F1,

F3

Resident Table1Temp

Where IsNull(F2);

UNQUALIFY *;

Table1:

LOAD

  F1,

  F2

Resident Table1Temp

Where Not IsNull(F2);

Drop Table Table1Temp;

10 Replies
sudeepkm
Valued Contributor III

Re: Non Matching records with NOT EXIST

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?

jaswantc
Contributor II

Re: Non Matching records with NOT EXIST

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

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,

F3

Resident Table2;

Qualify *;

Non_Matched:

LOAD

F1,

F3

Resident Table1Temp

Where IsNull(F2);

UNQUALIFY *;

Table1:

LOAD

  F1,

  F2

Resident Table1Temp

Where Not IsNull(F2);

Drop Table Table1Temp;

sibin_jacob
Contributor III

Re: Non Matching records with NOT EXIST

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

jaswantc
Contributor II

Re: Non Matching records with NOT EXIST

Thanks its worked.

jaswantc
Contributor II

Re: Non Matching records with NOT EXIST

Thanks this one also worked.

vivek_niti
Contributor

Re: Non Matching records with NOT EXIST

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.


jaswantc
Contributor II

Re: Non Matching records with NOT EXIST

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.

vivek_niti
Contributor

Re: Re: Non Matching records with NOT EXIST

Hi,

Please find the attached file.

Community Browser