Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
jmarotte
Contributor III
Contributor III

Contains and Replace

Hi, I've loaded my excel table and I need assistance with adding a contains and replace into my script.  For example I have a description column in my excel table that contains commentary.  I need to search for keywords from this column and replace it with a name.

So..for example, my 'Description' column looks like

1) Summary: Ticket Group: US_Team1 - on this date, xxx

2) Summary: Ticket Group: Asia_123Team - on this date, xxx

I need my script to search this Description column where if 'US_' exists then I need this new field to display Team 1 or if 'Asia_' exists I need this new field to display 123Team.  It needs to extract keyword between US_ and - or Asia_ and -

Hope this makes sense. Thank you.

3 Replies
Not applicable

It might not be super scalable, but you could try something like TextBetween() with nested if statements in your preceeding load. It'd require everything to follow the format you listed though, specifically in that the text you want to extract falls between the "US_" and " - " characters. But it might be a starting point, anyways!

LOAD Column1,

If(WildMatch(Column2,'*US_*'),TextBetween(Column2,'US_',' - '),

if(WildMatch(Column2,'*Asia_*'),TextBetween(Column2,'Asia_',' - '),)) as [Team Name]

INLINE [

Column1, Column2

1,Summary: Ticket Group: Asia_123Team - on this date, xxx

2,Summary: Ticket Group: US_Team1 - on this date, xxx

];

Edit: In thinking more about this, I supposed you could do without the nested if statements that check for the country name. That seems kind of pointless I guess. If you know that there's only one _ character in the string and it always follows the country name, then you'd just do something like this:

TextBetween(Column2, '_', ' - ')as [Team Name]

MarcoWedel

Hi,

one solution could be:

QlikCommunity_Thread_185287_Pic1.JPG

QlikCommunity_Thread_185287_Pic2.JPG

tabDelimiters:

LOAD * Inline [

Delimiter

Africa

Americas

Asia

Australia

Europe

US

];

mapDelimiters:

Mapping LOAD RecNo(),*

Resident tabDelimiters;

tabYourExcel:

LOAD *

Where Len(Team);

LOAD RecNo() as ID,

    TextBetween(Description,ApplyMap('mapDelimiters',IterNo())&'_',' ') as Team,

    ApplyMap('mapDelimiters',IterNo()) as Delimiter,

    *

Inline [

Description

1) Summary: Ticket Group: US_Team1 - on this date, xxx

2) Summary: Ticket Group: Asia_123Team - on this date, xxx

3) Summary: Ticket Group: Africa_234Team - on this date, xxx

4) Summary: Ticket Group: Americas_345Team - on this date, xxx

5) Summary: Ticket Group: Europe_456Team - on this date, xxx

6) Summary: Ticket Group: Australia_567Team - on this date, xxx

]

While IterNo()<=FieldValueCount('Delimiter');

hope this helps

regards

Marco

jmarotte
Contributor III
Contributor III
Author

TextBetween seemed to work. How do I add multiple TextBetweens? I've tried OR and it didn't work.