topic Re: set for a very long definition with multiple lines.. in QlikView

set for a very long definition with multiple lines..

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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 ?

Re: set for a very long definition with multiple lines..

Clever_Anjos — Wed, 31 Jan 2018 12:02:45 GMT

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,'&','$');

Re: set for a very long definition with multiple lines..

Anonymous — Wed, 31 Jan 2018 12:44:09 GMT

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 ' ?

Re: set for a very long definition with multiple lines..

Clever_Anjos — Wed, 31 Jan 2018 12:49:36 GMT

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

Re: set for a very long definition with multiple lines..

Anonymous — Wed, 31 Jan 2018 13:10:53 GMT

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,'&','$');

Re: set for a very long definition with multiple lines..

rwunderlich — Wed, 31 Jan 2018 13:16:18 GMT

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

Re: set for a very long definition with multiple lines..

Anonymous — Wed, 31 Jan 2018 13:38:05 GMT

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 ' ??

Re: set for a very long definition with multiple lines..

Clever_Anjos — Wed, 31 Jan 2018 13:51:38 GMT

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,'&','$');

Re: set for a very long definition with multiple lines..

Anonymous — Wed, 31 Jan 2018 14:09:47 GMT

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)','&','$');

Re: set for a very long definition with multiple lines..

Clever_Anjos — Thu, 01 Feb 2018 09:51:22 GMT

Good to read that, keep in touch