set for a very long definition with multiple lines..

[Anonymous]

Hi everybody,

I have a text box with a very long formula that I would like to reuse in multiple other text box.

So I would like to define a set like this:

set f_K_rest_to_pay='

if($(vNbMois_délai_Kx_rest_du) <=1 or IsNull($(vNbMois_délai_Kx_rest_du)), '',

' Tendance sur '

&

((year($(vMaxMMM_YY_délai_Kx_rest_du))-year(min(MMM_YY)))*12+(month($(vMaxMMM_YY_délai_Kx_rest_du))-month(min(MMM_YY))) +1)

&

' mois : ')

&

round(

(

(

Only(

..................................

)

*100,0.01)

&

if($(vNbMois_délai_Kx_rest_du) <=1 or IsNull($(vNbMois_délai_Kx_rest_du)), '',' %')

'

;

=> But it doesn't work. It seems that ' don't work, as I have also some inside my formula ?

Any advise ? Is it possible to have a set on a very long definition ?

[Clever_Anjos]

That´s because you´re using a $ and those are evaluated at script time

Use this workaround

set f_K_rest_to_pay='

if(&(vNbMois_délai_Kx_rest_du) <=1 or IsNull(&(vNbMois_délai_Kx_rest_du)), '',

' Tendance sur '

&

((year(&(vMaxMMM_YY_délai_Kx_rest_du))-year(min(MMM_YY)))*12+(month(&(vMaxMMM_YY_délai_Kx_rest_du))-month(min(MMM_YY))) +1)

&

' mois : ')

&

round(

(

(

Only(

..................................

)

*100,0.01)

&

if(&(vNbMois_délai_Kx_rest_du) <=1 or IsNull(&(vNbMois_délai_Kx_rest_du)), '',' %')

'

;

Let f_K_rest_to_pay = replace(f_K_rest_to_pay,'&','$');

[Anonymous]

Thanks a lot for this information that I didn't know. I corrected, but it still doesn't work.

I have the error message => Error: Garbage after expression: "tendance".

I don't understant how can qlik manage ' inside ' ?

[Clever_Anjos]

use same approach, choose another unused char (@ for example) and then use a later replace to fix the problem

[Anonymous]

Hi Clever,

Thanks for your help.

But even with a small formula like that, I get the message => Error: Garbage after expression: "tendance".

set f_K_rest_to_pay='if(&(vNbMois_délai_Kx_rest_du) <=1 or IsNull(&(vNbMois_délai_Kx_rest_du)), '',' Tendance sur '

)';

Let f_K_rest_to_pay = replace(f_K_rest_to_pay,'&','$');

[rwunderlich]

I think the problem is the variable ref in the replace, It should be quoted with DSE.

Let f_K_rest_to_pay = replace('$(f_K_rest_to_pay)','&','$');

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[Anonymous]

Oh yes... thanks Rob..

But still doesn't work.

When I reload, I get this error

=> Script line error :

Let f_K_restant_a_regler_tendance = replace('

if(&(vNbMois_délai_Kx_rest_du) <=1 or IsNull(&(vNbMois_délai_Kx_rest_du)), '',' Tendance sur '

)','&','$')

When I put directly the above Let, Tendance is red underligned, so it still seems to me, there should be a way to put ' inside ' ??

[Clever_Anjos · Accepted solution]

Try using square brackets

Set f_K_restant_a_regler_tendance = [

your expression here without $ using &

];

Let f_K_rest_to_pay = replace(f_K_rest_to_pay,'&','$');

[Anonymous]

Finaly works!

Thanks Rob and Clever for your time!

set f_K_rest_to_pay=[if(&(vNbMois_délai_Kx_rest_du) <=1 or IsNull(&(vNbMois_délai_Kx_rest_du)),'','Tendance sur ')];

Let f_K_rest_to_pay= replace('$(f_K_rest_to_pay)','&','$');

[Clever_Anjos]

Good to read that, keep in touch