like function

[#1]

is there a like function in qlikview?

I have a column called "bonus_description" and it has both letters and numbers in it, for example "new member 100% bonus"

what I want is to replace all "new member 100% bonus" to just "100%" and then do the same where there is 50%, 75% etc

[#2]

now half of "200% Deposit Bonus PKL" is showing, sometimes it's 200% and sometimes it's just the text

[Colin-Albert]

Belt and Braces, this trims any leading or training spaces from bonus_description first.

if(index(trim(bonus_description), '%') < 5,

      subfield(trim(bonus_description), '%') & '%',

      trim(mid(trim(bonus_description), index(left(trim(bonus_description), index(trim(bonus_description), '%')), ' ', -1),

      (index(trim(bonus_description), '%')+1 - index(left(trim(bonus_description), index(trim(bonus_description), '%')), ' ', -1) )

      ))

) 

[#4]

I'm still not getting the desired result on every row

[rustyfishbones · Accepted solution]

try this Zainab

[Colin-Albert]

The best way to test this is by putting the expression in a chart, and adding the different stages of the function as chart expressions. This way you can see the results without reloading your script. Once the expression works, copyit into the script.

You may need to add some other tests like the if(index(trim(bonus_description), '%') < 5, which will identify records that start X&, XX% or XXX% but not others such as A X% BBB CCCC DDD

It may be simpler to use a left expression to remove everything after the %, then use right to select the last Y characters, then mop up the oddities.

Can you show some more of the data that fails rather than drip feeding the exceptions one by one.

[#7]

thanks Alan, it worked perfectly

[#8]

Hi Alan

sorry for borthering you further... your code worked perfectly but it's for blank space ' ' and underscore '_' , I was wondering how one would add hyphen '-' to it, for example if I had a bonus called F-50%

[rustyfishbones]

Hi Zainab,

Again, there are various ways, but try adding some more REPLACE parts.

See the attached file

[#10]

thank you 

[rustyfishbones]

your welcome!