Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Use a variable declared in SQL into Qlikview charts

Hello , I have created 2 Date variables Start date and End date and declared them in SQL script itself to limit the number of rows that are loaded.

So I do get correct data for tables when I simply add calendardate as dimension.Bbut when I try to use these variables individually in set analysis expressions, it doesn't seem to work.

e.g.: Sum ({<column_1={'value1'},calendardate ={"$(vStartnDate)"}>} Sales)

I am thinking if variables declared in SQL have different usage or syntax?

Thank you.

1 Solution

Accepted Solutions
Not applicable
Author

So finally i could get it to work , all I had to do was, put it all in a num() and it works as expected.

View solution in original post

19 Replies
maxgro
MVP
MVP

if I understand the question

variables declared in QlikView can be used in SQL part of the load statement, in set analysis, etc....

variables declared in SQL (how? can you post the script?) I think can't be used in QlikView

may be you only have a problem with the date format of your vStartnDate variable

Not applicable
Author

Apologies I should have been more clear.

True, the variables are declared/created in Qlikview;  Settings->Variable Overview(the usual mehtod), but am using them in load statement directly.

e.g : select t.column1,t.column2

from table1 t

where t.calendate between $(vStartdate) and $(vEndDate);

And I have two input boxes in the sheet where I input the dates. Loading the data this way works fine.

But when I try to use these variables in set analysis expressions thats when it returns 0 as result.

Not having format troubles with input boxes, but any suggestions how can I make my formats uniform?

Thanks!

MK_QSL
MVP
MVP

Try to declare/create the variable in the script itself before using them in SQL load statement.

Let vStartDate = .....

Let vEndDate = ....

Not applicable
Author

Hi Manisha,

I tried: LET vStartDate = Calendardate;

(when above did not work I tried) LET vStartDate = ;

But it gives missing expression in both the scenarios. I added it once after connection string and once before the connection string but got the same error each time.

Can you please be more specific as to what exactly means '........' and where should I declare these. Thanks!

Anonymous
Not applicable
Author

It doesn't matter where the variable was created - in the load script or on the front end.  Check you syntax.  In set, dates are "format-sensitive", maybe this will work:

={"=date($(vStartnDate))"}

Not applicable
Author

Hi Michael, unfortunately that did not work following is the exact expression i used based on your suggestion!

Sum ({<column_1={'value1'},calendardate ={"=date($(vStartnDate))"}>} Sales)

Anonymous
Not applicable
Author

Whatever the reason is, it's not where the variable was created.  Can you upload your app to troubleshoot?

vishnus85
Partner - Creator
Partner - Creator

Hi Sharma,

Did you verify if the expected value is coming in the variable at presentation side.

Create a textbox, and give the expression as =vStartnDate and see what value it is showing. I feel that somehow the variable is not getting its expected value.

Not applicable
Author


The variable is getting the date correctly since if I simply input the date in input box and load a table with calendardate as a dimension vs Sales value am getting the expected Sales data for that date.

This variable gives 0 value only when I try to use it in Set analysis expressions.