Find particular string in a field from mapping sheet

[Anonymous]

Dear Friends

We have a long field , from which we want to search a particular city.

If the City from the mapping sheet is found in the Long field got to display the City .

Example :

In attached Excel , have got City names in first sheet - City

and Long field  in which we need to search if City name is available.

Like Mumbai from city sheet is available in long field.

So got to display Mumbai in new column.

Kindly help me achieve the result .

Thanks.

[breno_morais]

Hello Chintan.

Do you can attach this document for download?

[Anonymous]

Have attached sample excel

[Sergey_Shuklin]

Hello!

You can try to use Index() function for searching current cities in the string. If it > 0 then return city.

[breno_morais]

Look this:

Fields:

LOAD City

FROM [lib://Downloads/Sample.xlsx]

(ooxml, embedded labels, table is City);

JOIN LOAD Field FROM [lib://Downloads/Sample.xlsx]

(ooxml, embedded labels, table is [long field]);

Relation:

LOAD

City,

    IF(SubStringCount(Field, City) >= 1, 'Found', Field) as Contains

Resident Fields;

Did I help you? Mark correct answer helps me a lot

[stigchel]

You can also use the '/' seperator to split the values first, then a map will pick out just the cities:

CityMap:

Mapping

LOAD *, City as From Inline [City

Mumbai

Delhi

Kolkata

];

Load *,ApplyMap('CityMap',SubField(Field,'/'),Null()) as City Inline [Field

Campaign/Big/Sale/Mumbai/...............................

Campaign/Big/Sale/…………../.............................../Delhi

];

[balabhaskarqlik]

May be try like this:

City:

Mapping LOAD City

FROM [lib://Downloads/Sample.xlsx]

(ooxml, embedded labels, table is City);

NoConcatenate

LongField:

LOAD Field FROM [lib://Downloads/Sample.xlsx]

(ooxml, embedded labels, table is [long field]);

Temp:

Load    *,

    Subfield(ApplyMap('City',City,'Not Found'),'/',3) as City,

    ApplyMap('City',SubField(Field,'',3),'Not Found') as City

Resident LongField;

Drop table LongField;

[Anonymous]

Hello Piet

Thanks for your help. However its not necessary to have only '/' as seperator.

The record could also be as below where there are '_',  '-' which carries name of the City.

Is it possble to search those as well?

Campaign/Big/Sale/sfkfksfh-?pkg=sfsfkhfs_Zopsfslh-Mumbai/...............................

Regards

Chintan

[Anonymous]

Hello  Breno

Thanks for your reply. We do not want to know if it only contains.

We want to display the city name in different field.

Also city name could be anywhere as below: There is no particular order.

Campaign/Big/Sale/sfkfksfh-?pkg=sfsfkhfs_Zopsfslh-Mumbai/...............................

[stigchel · Accepted solution]

Ok then we create our own seperator '@' first and then use textbetween:

CityMap:

Mapping

LOAD *, '@' & City & '@' as From Inline [City

Mumbai

Delhi

Kolkata

];

Load *,TextBetween(MapSubString('CityMap',Field),'@','@') as City Inline [Field

Campaign/Big/Sale/Mumbai/...............................

Campaign/Big/Sale/…………../.............................../Delhi

Campaign/Big/Sale/sfkfksfh-?pkg=sfsfkhfs_Zopsfslh-Mumbai/...............................

];