topic Re: replacing string in QlikView

replacing string

Anonymous — Sun, 03 Nov 2013 17:23:15 GMT

hi guys

i have a few formulas one of them look like - (sum(x)+avg(z))/sum(y)

I need to change the formula to a set analysis something like -

sum({<date={"<$(Max2)>$( Max1)"}>}+ avg({<date={"<$(Max2)>$( Max1)"}>}/

sum({<date={"<$(Max2)>$( Max1)"}>}

I managed to do it with the "replace()" func -

replace(String,'sum(','sum({<date={"<$(Max2)>$(Max1)"}>}')

But I need some kind of “replaceif” condition that will check if the string is ‘sum(‘ or ‘avg(’

if((string='sum(',

replace(string,'sum(','sum({<date={"<$(Max2)>$(Max1)"}>}'),
if((string)='Avg(',
replace(string,'Avg(','Avg({<date={"<$(Max2)>$(Max1)"}>}'),

any ideas?

THX

Re: replacing string

Jason_Michaelides — Sun, 03 Nov 2013 17:39:55 GMT

I assume this is in the script? If so you can use MapSubString.

Hope this helps,

Jason

Re: replacing string

Anonymous — Mon, 04 Nov 2013 09:01:21 GMT

nope this is in the Gui

Re: replacing string

tresB — Mon, 04 Nov 2013 09:08:56 GMT

Isn't your second expression(with proper braces) working?

if((string='sum(',

     replace(string,'sum(','sum({<date={"<$(Max2)>$(Max1)"}>}'),
if((string='Avg(',   // Edit: brace removed
           replace(string,'Avg(','Avg({<date={"<$(Max2)>$(Max1)"}>}')

)

Re: replacing string

Anonymous — Mon, 04 Nov 2013 09:22:53 GMT

no cause i don't know in advance the index for the 'sum(' or 'avg('

plus my expression will only work on the sum( it wont work on both sum( + avg(

Re: replacing string

tresB — Mon, 04 Nov 2013 09:27:03 GMT

Can you please explain your exact requirement with a sample?

Re: replacing string

Anonymous — Mon, 04 Nov 2013 09:50:35 GMT

I have a string field named formula, this field has all kinds of formulas for example :

(sum(x)+avg(z))/sum(y)

sum(y)

In order to make a certain calculation I need to use set analysis so I used the ‘replace()’ string function for example

replace(String,'sum(','sum({<date={"<$(Max2)>$(Max1)"}>}')

My problem is I don’t know in advance how the formula will look. it could have a sum function or avg function or max function or both…

I need to check the string and replace each string that looks like’ sum(‘ or’ avg(‘ or ‘max( ‘

To :

'sum({<date={"<$(Max2)>$(Max1)"}>}’ or ‘avg({<date={"<$(Max2)>$(Max1)"}>}’ or

max({<date={"<$(Max2)>$(Max1)"}>}

Re: replacing string

tresB — Mon, 04 Nov 2013 10:03:23 GMT

May be using REPLACE recursively, like:

replace(

replace(String,'avg(','avg({<date={"<$(Max2)>$(Max1)"}>}')),

'sum(','sum({<date={"<$(Max2)>$(Max1)"}>}')),

'max(','max({<date={"<$(Max2)>$(Max1)"}>}'))

Re: replacing string

Jason_Michaelides — Mon, 04 Nov 2013 10:06:23 GMT

Use MapSubstring:

Map_Expressions:

MAPPING LOAD * INLINE [

In,Out

sum(,sum({<date={"<$(Max2)>$(Max1)"}>}

avg(,avg({<date={"<$(Max2)>$(Max1)"}>}

max(,max({<date={"<$(Max2)>$(Max1)"}>}

];

Then use

MapSubstring('Map_Expressions',String) AS Expression

Hope this helps,

Jason

Re: replacing string

Anonymous — Mon, 04 Nov 2013 21:36:48 GMT

could you give an example i'm not familiar with this function

thanks alot

Re: replacing string

Jason_Michaelides — Mon, 04 Nov 2013 21:53:45 GMT

I've given an example in my previous reply.

Re: replacing string

Anonymous — Tue, 05 Nov 2013 10:32:56 GMT

Sorry i didn't see

Thanks a lot it worked perfect