topic Re: How to highlight rows which have common values? in QlikView

How to highlight rows which have common values?

Thu, 16 Oct 2014 11:01:26 GMT

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.

Re: How to highlight rows which have common values?

maleksafa — Thu, 16 Oct 2014 11:07:37 GMT

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

Re: How to highlight rows which have common values?

Thu, 16 Oct 2014 11:09:04 GMT

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

Re: How to highlight rows which have common values?

Thu, 16 Oct 2014 11:18:23 GMT

Please help

Re: How to highlight rows which have common values?

maleksafa — Thu, 16 Oct 2014 11:20:03 GMT

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

LOAD @1 as ID,

@2 as NAME,

@3 as CustomerID

FROM

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

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

Re: How to highlight rows which have common values?

Thu, 16 Oct 2014 11:31:09 GMT

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

Please can sort this.

Re: How to highlight rows which have common values?

maleksafa — Thu, 16 Oct 2014 11:33:17 GMT

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

Re: How to highlight rows which have common values?

Thu, 16 Oct 2014 11:38:38 GMT

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

Please suggest me

Re: How to highlight rows which have common values?

maleksafa — Thu, 16 Oct 2014 12:11:01 GMT

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

Re: How to highlight rows which have common values?

Thu, 16 Oct 2014 12:54:51 GMT

Thanks!