topic Re: Loading Variables - Script line error in QlikView

Loading Variables - Script line error

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

Hi All,

I am trying to load variables from excel spread sheet by for loop however, getting script line error while loading.

Variable looks like vName = Num(Sum({<Type ={'A'}, Year = {$(=Max(Year))}>} Sales,'#,##0.0').

I think, getting error due to single quotations.

Could you please assist on this.

Thanks,

Nihhal.

Re: Loading Variables - Script line error

Anil_Babu_Samineni — Thu, 31 May 2018 13:42:26 GMT

May be this?

LET vName = '=Num(Sum({<Type ={'A'}, Year = {$(=Max(Year))}>} Sales,'#,##0.0')';

Re: Loading Variables - Script line error

avkeep01 — Thu, 31 May 2018 13:42:38 GMT

First option to try, replace quotes and dollar signs with CHR(39) en CHR(36) (respectively ' and $).

SET vName = 'Num(Sum({<Type ={'&CHR39 &'A'&CHR(39)&'}, Year = {'&CHR(36)'&(=Max(Year))}>} Sales,'&CHR(39)&'#,##0.0'&CHR(39)&')';

Re: Loading Variables - Script line error

Miguel_Angel_Baeyens — Thu, 31 May 2018 14:33:38 GMT

To "escape" the single quote sign, use another one in front, so in your expression:

LET vName = 'Num(Sum({<Type ={''A''}, Year = {$(=Max(Year))}>} Sales,''#,##0.0'')';

But using LET evaluates the expression on the right of the "=" before assigning the value to the variable, and since it is valid syntax, the part $(=Max(Year)) will be evaluated. It does not have any value during the script, so the result in the variable overview after the load will be

Num(Sum({<Type ={'A'}, Year = {}>} Sales,'#,##0.0')

I would use SET instead of LET and use Chr() function as mentioned above by A.M.

Re: Loading Variables - Script line error

nihhalmca — Thu, 31 May 2018 14:56:52 GMT

Hi Miguel,

I am loading 130 variables and changing quotes by chr(39) is time consuming so i tried to add one more quote front and tried to add front and back as well but no luck.

Re: Loading Variables - Script line error

Miguel_Angel_Baeyens — Thu, 31 May 2018 15:12:05 GMT

I can only suggest you to use some text editor with Find and Replace and copy back to the script.

It likely didn't work because of the reason mentioned above, the set analysis, actually any expression in the form $() or $(=) will be evaluated prior to be assigned. During the script, most of these values are non existing, so the $() is expanded into null or garbage.

Re: Loading Variables - Script line error

nihhalmca — Thu, 31 May 2018 15:20:51 GMT

Hi Miguel,

No worries about nulls because i will store those into variables. I am thinking about your first suggestion ""escape the single quote sign, use another one in front", is it possible?

Re: Loading Variables - Script line error

Miguel_Angel_Baeyens — Thu, 31 May 2018 16:12:47 GMT

Yes it is, use the example I wrote above. But the issue with the expressions will persist: as soon as the script finds a $() or $(=) the contents between the brackets will be evaluated and the expression will not be kept.

Re: Loading Variables - Script line error

nihhalmca — Fri, 01 Jun 2018 13:24:21 GMT

Hi A.M - I tried and did not work, think i am doing mistake somewhere else.

I am coping my actual variables here, could you edit it please.

1. VariableName = vName

1. VariableValue = num(sum({<Type={'Actual'}, Quarter={$(=min(Quarter,1))}>} Sales,'#,##0.0;(#,##0.0)')

2. VariableName = vLabel

2. VariableValue = Only({<Quarter={$(=min(Quarter,1))}>} QuartrandYear) &chr(13)&'(A)'

Thanks,

Nihhal.

Re: Loading Variables - Script line error

Miguel_Angel_Baeyens — Fri, 01 Jun 2018 13:42:09 GMT

LET vName = 'Num(Sum({<Type ={''Actual''}, Quarter = {' & Chr(36) & '(=Min(Quarter))}>} Sales,''#,##0.0'')';

Re: Loading Variables - Script line error

nihhalmca — Fri, 01 Jun 2018 14:03:08 GMT

Thanks Miguel.

For second variable, it would be

'Only({<Quarter={'&chr(36)&'(=min(Quarter,1))}>} QuartrandYear) &chr(13)&''(A)''' - right?

Re: Loading Variables - Script line error

Miguel_Angel_Baeyens — Fri, 01 Jun 2018 14:08:46 GMT

Looks about right, yes