Searching word in string

[Anonymous]

Hi,

I have 2 files where in one file i have a string and in other file have keyword. if Keyword is exists in string then will get the country.

How can i compare 2 files?

PFA of sample files.

[JonnyPoole]

Here is one way. This method would include all hints, not just the 'first' one.  How many records in each file ? 

Result:

Load script:

--------------------

String:

LOAD ID,

    String,

    1 as JoinKey

FROM

(ooxml, embedded labels, table is Sheet1);

join (String)

LOAD Text,

    Country,

    1 as JoinKey

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

result:

load

  ID,

  String,

  Country

Resident String

  where SubStringCount(String,Text)>0;

  drop table String;

[Anonymous]

Solution using Keepchar function:

Table1:

LOAD ID, String FROM String.xlsx (ooxml, embedded labels, table is Sheet1);

join

LOAD Text, Country FROM Mapping.xlsx (ooxml, embedded labels, table is Sheet1);

Result:

NoConcatenate LOAD * Resident Table1 Where KeepChar(String, Text)=Text;

DROP Tables Table1;

Marc.

[Anonymous]

Hi Jonathan,

My 'String' file has many values, this logic is not working for me. Here we are using mapping file to get all the countries from string file. It has thousands of strings. Can you please tell me how can i do this?

[maxgro]

Re: Mapping question. Linking two tables

[kuba_michalik]

Cartesian products are bad

This problem begs for use of MapSubstring. It does not get used too often, so all the more reason 😉

map:

Mapping LOAD

     text, '.'&country&';'

From Mapping.xlsx (ooxml, embedded labels, table is Sheet1);

result:

LOAD

     ID,

     Mid(splitString, Index(splitString,'.')+1) as Country

Where Index(splitString, '.');

;

LOAD

     ID,

     Subfield(mappedString, ';') as splitString

;

LOAD

     ID,

     MapSubstring('map', String) as mappedString

From String.xlsx (ooxml, embedded labels, table is Sheet1);

Above code assumes your strings do not contain dots and semicolons. Choose your own delimiter chars if this is not the case.