How to highlight rows which have common values?

[#1]

Hi,

I have below columns in excel

A Excel:

ID       Name      CustomerID

342324   dsf       4567

974742   jgfhg     4567

564566   gre       4567

857566   vb        4567

676766   kijuh     4567

868787   tred      4567

656765   ponnj     2341

676776   trtr      2341

454656   affgf     2341

907677   lhoyok    2341

4545       ghtehet   9087

3443543 yhyrh       9087

5454534  gtr          9087

I have another excel file with below details

B Excel:

RNO     

4567  

2341

5637

97544

75858

Like this there are millions of rows in both excel sheets. All I have to highlight the rows in A excel sheet which have common RNO from B Excel and CustomerID from A Excel

Please can anyone suggest me would it be possible with Qlikview file. Because it is very hectic to highlight each and every row manually.

Please can anyone help me.

Thanks.

[maleksafa]

you can use a mapping table to identify what are the rows that exist in A and B.

create a mapping table for excel B with first column as RNO and second column the value 1

apply the mapping between table A and B, if the records match it will give you the value 1, if it does not match you can add a default value which is 0. then you will be able to highlight the data with value = 1

[#3]

Thanks. Can you please give me script based on my above columns please!!

[#4]

Please help

[maleksafa · Accepted solution]

here you go, A.txt is your A excel and B.txt is your B excel

A:

LOAD @1 as ID,

     @2 as NAME,

     @3 as CustomerID

FROM

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

B:

Mapping LOAD @1,'1' as Flag

FROM

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

Final:

Load *, ApplyMap('B',CustomerID,'0') as Mapping Resident A;

drop table A

[#6]

Thanks. When I am loading this it is showing Field not found @1.

Please can sort this.

[maleksafa]

you should modify the column names of course they will not be found, can you upload a sample excel file for A and B?

[#8]

Thanks. I modified it. But how can I change the B table script if B table have more than one column

Actually in B tables there are 13 columns?

RNO

2

3

4

5

6

7

8

9

10

11

12

13

Please suggest me

[maleksafa]

if you want all 13 columns then create another table which will be used only for mapping (to note that the mapping table will be deleted automatically at the end of the script).

so you need to load your B excel twice, first time normally if you want to use the other columns in your data model, and another time just for the mapping name it BMapping which will have only the RNO column and the flag column

[#10]

Thanks!