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.

[puttemans]

Hi there,

I'd work with wildmatch. Don't know the amount of cities you have, and how you'd like to combine them.

Below script generates a table City and another one Long. The 'nr' field will link both.

City:

LOAD City,

// the same order as on line 24, so Mumbai = 1, Kolkata = 2, etc...

     nr

FROM

[x\Sample (1).xlsx]

(ooxml, embedded labels, table is City);

NoConcatenate

Long:

LOAD Field,

wildmatch(Field, '*Mumbai*', '*Kolkata*', '*Delhi*') as nr

FROM

[x\Sample (1).xlsx]

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

Regards,

Johan

[Anonymous]

Thanks Piet for your help.

I am getting error as 'MAPPING requires 2-column input '

Do I need to change anything if I am not using Inline table and fetching an excel mapping sheet.

Below is the script I am using.

Mapping_Destination:

Mapping LOAD *,

   '@' & Destination & '@' ,

    Column

FROM

[Destination_Sheet.xlsx]

(ooxml, embedded labels, table is Sheet1)

Raw_Data:

LOAD

//     [Assigned To],

     BU,

     [Country Name],

     TextBetween(MapSubString('Mapping_Destination',[Website Enquiry Landing]),'@','@') as New_Destination ,

      from Aug_01_2018__Data.xlsx;

[Peter_Cammaert]

Add the following to the end of Breno Morais' (who did all the work) code:

FieldContains:

LOAD Field, Concat(City, ', ') AS IncludedCities

RESIDENT Relation

WHERE Contains = 'Found'

GROUP BY Field;

Then create a TableBox with fields Field and IncludedCities.

[stigchel]

Yes a mapping load has always two columns, say From and To. Load the city twice, once the City itself as the From column and once as '@' & City & '@' as the mapping To column

[Anonymous]

Thanks Piet, am able to achieve the result.