topic Re: like function in QlikView

like function

Fri, 31 Jan 2014 09:46:41 GMT

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

Re: like function

alexandros17 — Fri, 31 Jan 2014 09:49:14 GMT

Use match or wild match (see help)

Re: like function

Fri, 31 Jan 2014 09:50:42 GMT

try with wildmatch() function...

look it on qv help

Re: like function

tresB — Fri, 31 Jan 2014 09:51:05 GMT

Use Keepchar() / PurgeChar() function.

Re: like function

maxgro — Fri, 31 Jan 2014 09:52:22 GMT

=replace(' new member 100% bonus','new member', '')

Re: like function

Colin-Albert — Fri, 31 Jan 2014 09:55:50 GMT

Try

trim(replace(replace(bonus_description,'new member',''), 'bonus','')) as bonus_percent

Re: like function

jvishnuram — Fri, 31 Jan 2014 10:01:36 GMT

Hi Abbasi,

You can use Keepchar() function.

Use like this in your script

Keepchar('bonus_description','0123456789%').,

it will fetch all the records like you want.

Re: like function

rustyfishbones — Fri, 31 Jan 2014 10:09:07 GMT

Hi Zainab,

there are a number of options

Here are 2

PURGECHAR()

KEEPCHAR()

Re: like function

Fri, 31 Jan 2014 10:09:31 GMT

thanks

Re: like function

alexandros17 — Fri, 31 Jan 2014 10:10:11 GMT

Keep char works in this way:

keepchar ( 'a1b2c3','123' ) returns '123'

This is not "like" ...

Am I right?

Re: like function

rustyfishbones — Fri, 31 Jan 2014 10:18:33 GMT

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

Re: like function

Fri, 31 Jan 2014 10:20:02 GMT

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

Re: like function

Fri, 31 Jan 2014 10:40:53 GMT

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

Re: like function

rustyfishbones — Fri, 31 Jan 2014 11:02:24 GMT

Hi Zainab,

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

Regards

Alan

Re: like function

Fri, 31 Jan 2014 11:14:56 GMT

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

Re: like function

Colin-Albert — Fri, 31 Jan 2014 11:42:50 GMT

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.

Re: like function

Colin-Albert — Fri, 31 Jan 2014 11:52:02 GMT

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) )

))

Re: like function

Fri, 31 Jan 2014 12:18:05 GMT

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

Re: like function

Colin-Albert — Fri, 31 Jan 2014 12:24:40 GMT

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) )

))

)

Re: like function

Colin-Albert — Fri, 31 Jan 2014 12:26:28 GMT

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) )

))

)