Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
catrauth
Contributor II
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
Contributor II
Contributor II
Author

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.

View solution in original post

7 Replies
Digvijay_Singh

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
Specialist
Specialist

is that..

Capture.PNG

Capture.PNG

catrauth
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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.