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

[rustyfishbones]

to answer your question.

You can use LIKE in Qlikview

However, the above will not give you exactly what you need regarding solving the issue you have, but you can use LIKE

HTH

Alan

[#3]

Hi Alan,

keepchar did clean up some of the data but I also have some bonuses where there are numbers besides the percent like "20130630 50% bonus" and "44% 23.03"

how do I remove 20130630 without removing 50%? I can use replace but there are many variations of this problem so I'm looking for an easier  and not so time consuming solution

[#4]

if takes 2-3 parameters but I have 9, is there a case when kind of function I could use?

[rustyfishbones]

Hi Zainab,

Can you send me a list of the variations please and I will take a look

Regards

Alan

[#6]

New_customers_100%_Deposit_Bonus

200% Deposit Bonus PKL

PKL 50% Deposit Bonus, 2nd Dep

20130306 50% Deposit Bonus

PKL 44% Deposit Bonus 23.03

[Colin-Albert]

You can use the index() function to find the character position of the % and then find the position of the space before that and slice the text between these positions.

[Colin-Albert]

This uses index() function to find the character position of the % and then find the position of the space before that and slice the text between these positions.

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

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

))

[#9]

Colin when I use your code, it only works when there are spaces around %, not for for ex

New_customers_100%_Deposit_Bonus

200% Deposit Bonus PKL

[Colin-Albert]

if(index('200% test bonus', '%') < 5,

      subfield('200% test bonus', '%') & '%',

      trim(mid('200% test bonus', index(left('200% test bonus', index('200% test bonus', '%')), ' ', -1),

           (index('200% test bonus', '%')+1 - index(left('200% test bonus', index('200% test bonus', '%')), ' ', -1) )

      ))

)

[Colin-Albert]

That should have been

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

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

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

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

      ))

)