topic Searching word in string in QlikView

Searching word in string

Anonymous — Wed, 22 Oct 2014 14:48:52 GMT

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.

Re: Searching word in string

JonnyPoole — Wed, 22 Oct 2014 15:02:10 GMT

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;

Re: Searching word in string

Anonymous — Wed, 22 Oct 2014 15:09:51 GMT

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.

Re: Searching word in string

Anonymous — Wed, 22 Oct 2014 17:54:24 GMT

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?

Re: Searching word in string

maxgro — Wed, 22 Oct 2014 18:04:30 GMT

Re: Mapping question. Linking two tables

Re: Searching word in string

kuba_michalik — Wed, 22 Oct 2014 18:23:51 GMT

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.