Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
pradeepm
Contributor II
Contributor II

Searching word in string

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.

5 Replies
JonnyPoole
Employee
Employee

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

Result:

Capture.PNG.png

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;

marcarreras
Specialist
Specialist

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.

pradeepm
Contributor II
Contributor II
Author

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
MVP
MVP

kuba_michalik
Partner
Partner

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.