Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
now half of "200% Deposit Bonus PKL" is showing, sometimes it's 200% and sometimes it's just the text
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) )
))
)
I'm still not getting the desired result on every row
try this Zainab
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.
thanks Alan, it worked perfectly
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%
Hi Zainab,
Again, there are various ways, but try adding some more REPLACE parts.
See the attached file
thank you
your welcome!