topic Find particular string in a field from mapping sheet in QlikView

Find particular string in a field from mapping sheet

Anonymous — Thu, 23 Aug 2018 12:26:53 GMT

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.

Re: Find particular string in a field from mapping sheet

breno_morais — Thu, 23 Aug 2018 12:31:13 GMT

Hello Chintan.

Do you can attach this document for download?

Re: Find particular string in a field from mapping sheet

Anonymous — Thu, 23 Aug 2018 12:35:08 GMT

Have attached sample excel

Re: Find particular string in a field from mapping sheet

Sergey_Shuklin — Thu, 23 Aug 2018 12:42:11 GMT

Hello!

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

Re: Find particular string in a field from mapping sheet

breno_morais — Thu, 23 Aug 2018 14:06:59 GMT

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

Re: Find particular string in a field from mapping sheet

stigchel — Thu, 23 Aug 2018 14:39:09 GMT

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

];

Re: Find particular string in a field from mapping sheet

BalaBhaskar_Qlik — Thu, 23 Aug 2018 14:41:49 GMT

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;

Re: Find particular string in a field from mapping sheet

Anonymous — Fri, 24 Aug 2018 05:18:47 GMT

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

Re: Find particular string in a field from mapping sheet

Anonymous — Fri, 24 Aug 2018 06:22:29 GMT

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/...............................

Re: Find particular string in a field from mapping sheet

stigchel — Fri, 24 Aug 2018 09:41:50 GMT

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/...............................

];

Re: Find particular string in a field from mapping sheet

puttemans — Fri, 24 Aug 2018 10:00:03 GMT

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

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

Re: Find particular string in a field from mapping sheet

Anonymous — Fri, 24 Aug 2018 10:28:51 GMT

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;

Re: Find particular string in a field from mapping sheet

Peter_Cammaert — Fri, 24 Aug 2018 10:57:19 GMT

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.

Re: Find particular string in a field from mapping sheet

stigchel — Fri, 24 Aug 2018 11:19:35 GMT

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

Re: Find particular string in a field from mapping sheet

Anonymous — Fri, 24 Aug 2018 11:22:34 GMT

Thanks Piet, am able to achieve the result.