topic Re: Contains and Replace in QlikView

Contains and Replace

Anonymous — Fri, 02 Oct 2015 19:43:47 GMT

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.

Re: Contains and Replace

Fri, 02 Oct 2015 20:09:41 GMT

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]

Re: Contains and Replace

MarcoWedel — Fri, 02 Oct 2015 22:21:12 GMT

Hi,

one solution could be:

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

Re: Contains and Replace

Anonymous — Mon, 05 Oct 2015 15:52:19 GMT

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