Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
puttemans
Valued Contributor

Re: Find particular string in a field from mapping sheet

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

qlikviewchintan
Contributor

Re: Find particular string in a field from mapping sheet

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

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.

Partner
Partner

Re: Find particular string in a field from mapping sheet

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

qlikviewchintan
Contributor

Re: Find particular string in a field from mapping sheet

Thanks Piet, am able to achieve the result.