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

pranaview — Wed, 16 Jun 2021 05:42:56 GMT

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 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!

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

yassinemhadhbi — Wed, 16 Jun 2021 08:11:25 GMT

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

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

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

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