Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

catrauth
New Contributor II

Read in variables for expressions in Qlik Sense

I have created a Qlik Sense app to read in variables to use in the expressions for date ranges.

After reading in the variables from a spreadsheet and applying them to the relevant expression they are not reducing the data.

The same expressions built into the app appear to work correctly.

Any idea what is going wrong?

The app and the data are attached.

1 Solution

Accepted Solutions
catrauth
New Contributor II

Re: Read in variables for expressions in Qlik Sense

The problem turns out to be in the Master Variables spreadsheet.

The vStartDate and vEndDate both require an = sign at the beginning.

In Excel you need to enter the = sign when you enter the expression otherwise it will try to evaluate the expression and Excel wil not lie it and give you an error.

7 Replies
Digvijay_Singh
Honored Contributor III

Re: Read in variables for expressions in Qlik Sense

Looks like your script is not working, I don't see '~' is replaced by '$'. See the image of variables, also why are you loading variable xlsx twice? -

Capture.PNG

Variables:

LOAD

    Name,

    "Definition",

    "Replace"

FROM [lib://Data Files/All dates data.xlsx]

(ooxml, embedded labels, table is [Master Variables]);


//Why 2nd time load below?


LOAD

    Name,

    "Definition",

    "Replace"

FROM [lib://Data Files/All dates data.xlsx]

(ooxml, embedded labels, table is [Master Variables]);

zebhashmi
Valued Contributor

Re: Read in variables for expressions in Qlik Sense

is that..

Capture.PNG

Capture.PNG

catrauth
New Contributor II

Re: Read in variables for expressions in Qlik Sense

Mistake on my part there should not be two load statements.

That is not the problem though.

'~' is replaced by '$' is  in the  For loop just before the else statement.

catrauth
New Contributor II

Re: Read in variables for expressions in Qlik Sense

As pointed out previously a mistake on my part but does not resolve the original problem.

Once read in the variable do not reduce the data correctly in the charts.

Digvijay_Singh
Honored Contributor III

Re: Read in variables for expressions in Qlik Sense

This bold line change start replacing '~' to '$' as needed -

FOR i = 0 TO NoOfRows('Variables') -1

  LET vVarName = Peek('Name', $(i), 'Variables');


  if match(Peek('Replace', $(i), 'Variables'), 'Yes') then

   LET vTemp = Peek('Definition', $(i), 'Variables');

   SET $(vVarName)_temp = '$(vTemp)';


   LET $(vVarName) = replace($(vVarName)_temp, '~', '$');

  else


SET $(vVarName) = $(vTemp);

  end if


NEXT

catrauth
New Contributor II

Re: Read in variables for expressions in Qlik Sense

Changing to LET does not resolve the problem.

On the Call sheet try start date of 8/1/2015 and end date if 10/31/2015.

You will see the chart on the right returns two records.

If you substitute the Variable tab in the script for the Start and End Calendars and reload you will see the correct answer is returned.

catrauth
New Contributor II

Re: Read in variables for expressions in Qlik Sense

The problem turns out to be in the Master Variables spreadsheet.

The vStartDate and vEndDate both require an = sign at the beginning.

In Excel you need to enter the = sign when you enter the expression otherwise it will try to evaluate the expression and Excel wil not lie it and give you an error.

Community Browser