Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pranaview
Creator III
Creator III

How to check the non matching values in one table referring another

 

Hi All,

Requirement: I have two tables, let's call them Table A and Table B. For this requirement I'll be referring only two columns from both tables i.e. Number and ID. I need to look for Numbers from Table A in the Table B and check if the ID(s) associated with the Number in Table B are not different in Table A.  Please keep in mind that there can be duplicates as well as multiple IDs associated with the same Number.

Let's look at the below scenario for better understanding.

Table A:

Number           ID

123                      1

124                      2

124                      3

125                      4

125                      4

125                      5

126                      6

127                      8

 

Table B:

Number             ID

123                       1

124                       2

124                       4

125                       5     

126                       7     

127                       8

127                       9

From this scenario - Numbers 124,126 and 127 falls under the issue category that I need to show the users.

In Table B there's an ID 4 which is related to Number 124 but not in Table A and 126 is straightforward as IDs don't match in both tables for this Number. For the Number 127, there's only one record in Table A but two in Table B and ID 9 is not present in Table A for the same Number.

123 is a straight match with  ID 1 and 125 has only onw record in Table B compared to 3 records in Table A albeit one being a duplicate but ID 4 is present in the Table A as well so this one is fine.

I hope i have made the requirement clear, if not then please let me know. Any suggestion or help will be appreciated.

Thanks!                     

1 Reply
yassinemhadhbi
Creator II
Creator II

Good morning,

I didn't understand your requirement clearly , but if you want to check value if they exist or no in the table B 

I recomend using MAPPING LOAD

If you want to check on the number only you can :

Map_num:MAPPING load Number, 1 as Exist from TABLEB;

and then you can check if this number exists while loading TABLE using applymap

Applymap('Map_num',number,0) as exists

And if you want to check on the two columns you can : 

Map_num_id:MAPPING load Number&'|'&ID as Key, 1 as Exist from TABLEB;

And then when you load data in TABLE A

Applymap('Map_num_id',Number&'|'&ID,0)

I hope this matches your requirement

Best Regards
Yassine Mhadhbi