Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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!
Try to declare/create the variable in the script itself before using them in SQL load statement.
Let vStartDate = .....
Let vEndDate = ....
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!
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))"}
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)
Whatever the reason is, it's not where the variable was created. Can you upload your app to troubleshoot?
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.
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.