How to search word in a sentence

[pra_kale]

Hi,

I have attached Two excel files below..

1 file contains the complete address in column 5.

2  file contains address in a 3 columns.

How i can search these 3 columns containing word in a address 5. It means if any word from that 3 columns is found in the complete address then in final output column remark should be Yes.

CHDRNUM	Name_1	Name_2	Name_3	Name_4	Name_5	Final output
12345678	Ramnagar	Shiv Mandir Road	Ghoad Bundar Road	Thane W	Maharshtra	Yes
9876774	Phadke road	Sadanand Palace	Joshi	CST	Maharshtra	Yes
5631233	ABC	CDE	EFG	HIJ	Andhra Pradesh
9876543	Matunga	ZZZ	AAA	Titwala	Nasik	Yes
9996633	ZXC	DFC	QWE	AXC	ZZZ

Please help to resolve the issue.

Thanks in Advance.

[sunny_talwar]

May be this?

B:

CrossTable(Address, Data)

LOAD 1 as Dummy,

  Address_1,

    Address_2,

    Address_3

FROM

[Address.xlsx]

(ooxml, embedded labels, table is Sheet1);

MappingTable:

Mapping

LOAD Data,

  '/Yes\' as Flag

Resident B;

DROP Table B;

A:

LOAD CHDRNUM,

    Name_1,

    Name_2,

    Name_3,

    Name_4,

    Name_5

FROM

[Details_Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

B:

LOAD *,

  TextBetween(MapSubString('MappingTable', complete_Address), '/', '\') as Flag;

LOAD *,

  Name_1 &'|'& Name_2 &'|'& Name_3 &'|'& Name_4 &'|'& Name_5 as complete_Address

Resident A;

DROP Table A;

[pra_kale]

Hi,

Thanks sunny for your help...it is working fine.

But, in actual data i have special characters in the address like Ramanagar/18..etc. sample file is attached for reference so if match found then in flag column i am getting out-put like 17/|/Yes  where as i only want Yes.

Secondly only change required is, if all the  3 words in the 3 columns are found in the complete address then only flag should be Yes. So i will get Yes in front of only 2 policy numbers as per attached sample.

CHDRNUM	Name_1	Name_2	Name_3	Name_4	Name_5
12345678	Ramnagar/16	Town Hall	KOLHAPUR	Thane W	Maharshtra
9876774	Phadke road	Sadanand Palace/17/	Joshi	Thane W	Maharshtra

Thanks in advance.

[sunny_talwar]

Try with a special character which you won't have in your address field

B:

CrossTable(Address, Data)

LOAD 1 as Dummy,

    Address_1,

    Address_2,

    Address_3

FROM

[Address.xlsx]

(ooxml, embedded labels, table is Sheet1);

MappingTable:

Mapping

LOAD Data,

  '^Yes^' as Flag

Resident B;

DROP Table B;

A:

LOAD CHDRNUM,

    Name_1,

    Name_2,

    Name_3,

    Name_4,

    Name_5

FROM

[Details_Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

B:

LOAD *,

  TextBetween(MapSubString('MappingTable', complete_Address), '^', '^') as Flag;

LOAD *,

  Name_1 &'|'& Name_2 &'|'& Name_3 &'|'& Name_4 &'|'& Name_5 as complete_Address

Resident A;

DROP Table A;

[pra_kale]

Hi Sunny,

great it is working..

But only one change is required in the application that is if all the conditions are satisfied then only it will give Yes in flag column means it will search all the three words from Address file and if all these tree words are exist in the complete address column then only flag should be Yes.

Thanks in advance.

[sunny_talwar · Accepted solution]

May be this

Mapping1:

Mapping

LOAD Address_1,

  '^Yes^' as Flag1

FROM

[Address.xlsx]

(ooxml, embedded labels, table is Sheet1);

Mapping2:

Mapping

LOAD Address_2,

  '^Yes^' as Flag2

FROM

[Address.xlsx]

(ooxml, embedded labels, table is Sheet1);

Mapping3:

Mapping

LOAD Address_3,

  '^Yes^' as Flag3

FROM

[Address.xlsx]

(ooxml, embedded labels, table is Sheet1);

A:

LOAD CHDRNUM,

    Name_1,

    Name_2,

    Name_3,

    Name_4,

    Name_5

FROM

[Details_Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

B:

LOAD *,

  PurgeChar(RangeMaxString(If(Len(Trim(Flag1)) = 0, 'Z', Flag1), If(Len(Trim(Flag2)) = 0, 'Z', Flag2), If(Len(Trim(Flag3)) = 0, 'Z', Flag3)), 'Z') as Flag;

LOAD *,

  TextBetween(MapSubString('Mapping1', complete_Address), '^', '^') as Flag1,

  TextBetween(MapSubString('Mapping2', complete_Address), '^', '^') as Flag2,

  TextBetween(MapSubString('Mapping3', complete_Address), '^', '^') as Flag3;

LOAD *,

  Name_1 &'|'& Name_2 &'|'& Name_3 &'|'& Name_4 &'|'& Name_5 as complete_Address

Resident A;

DROP Table A;

DROP Fields Flag1, Flag2, Flag3;

[pra_kale]

Hi,

Great Sunny it is working..

Just want to know whether it is searching  complete words in a particular column for e.g. in a Address_1 column of Address file i have "Town Hall" and " Joshi Marg" then it will search both this words together and if found then only it will give flag as Yes or it is searching "Town" separately and "Hall" separately and if found any where then give Yes.

Secondly whether it possible to remove case sensitive thing from the coding because in Details_Data file if "Town Hall" is written as "TOWN HALL'" then it is not giving Yes flag.

Thanks in advance.

[sunny_talwar]

1) Town Hall will always be searched together if they are single word. It won't look for Town and then Hall

2) You can use Upper or Lower or Capitalize functions to both Address and the checking fields so that they all have the same casing.

[pra_kale]

Thanks Sunny...Great..

It is working..