topic Re: Use a variable declared in SQL into Qlikview charts in QlikView

Use a variable declared in SQL into Qlikview charts

Thu, 05 Mar 2015 19:45:08 GMT

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.

Re: Use a variable declared in SQL into Qlikview charts

maxgro — Thu, 05 Mar 2015 20:11:56 GMT

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

Re: Use a variable declared in SQL into Qlikview charts

Thu, 05 Mar 2015 20:39:27 GMT

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!

Re: Use a variable declared in SQL into Qlikview charts

MK_QSL — Thu, 05 Mar 2015 21:01:54 GMT

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

Let vStartDate = .....

Let vEndDate = ....

Re: Use a variable declared in SQL into Qlikview charts

Thu, 05 Mar 2015 21:14:21 GMT

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!

Re: Use a variable declared in SQL into Qlikview charts

Anonymous — Thu, 05 Mar 2015 21:15:00 GMT

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))"}

Re: Use a variable declared in SQL into Qlikview charts

Thu, 05 Mar 2015 21:24:32 GMT

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)

Re: Use a variable declared in SQL into Qlikview charts

Anonymous — Thu, 05 Mar 2015 21:26:38 GMT

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

Re: Use a variable declared in SQL into Qlikview charts

vishnus85 — Thu, 05 Mar 2015 21:36:10 GMT

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.

Re: Use a variable declared in SQL into Qlikview charts

Thu, 05 Mar 2015 21:39:21 GMT

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.

Re: Use a variable declared in SQL into Qlikview charts

vishnus85 — Thu, 05 Mar 2015 21:54:50 GMT

Not sure what is happening.

Excuse me if I am being foolishly specific, but I see that in your question you have mentioned the expression you use as "Sum ({<column_1={'value1'},calendardate ={"$(vStartnDate)"}>} Sales)"

and later you have mentioned that you have used it in script side as

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

Hopefully it is not the unwanted 'n' in vStartnDate that makes all the trouble. It happened to me once. A small spelling mistake took a lot of debugging time. That is the reason I ask you this !

Re: Use a variable declared in SQL into Qlikview charts

Thu, 05 Mar 2015 21:57:00 GMT

Not a problem. Thanks....good catch!

That was my typo here in the discussion but it says vStartDate everywhere in the application.

Re: Use a variable declared in SQL into Qlikview charts

Thu, 05 Mar 2015 21:58:15 GMT

Hi Michael, amnot sure how i can mock the sql script to be able to upload the app here? Am pretty new to the qlikview world.

Re: Use a variable declared in SQL into Qlikview charts

Anonymous — Thu, 05 Mar 2015 22:31:21 GMT

No need to anything with the script, just upload your qvw file, if it is small. You can reduce app and scramble data before uploading.

To upload, click "use advance editor", it is on the right top of the "reply to..." window. After that, see "attach" link on the right bottom.

Re: Use a variable declared in SQL into Qlikview charts

Fri, 06 Mar 2015 14:43:16 GMT

Sorry not able to reudce the data, if its not too much to ask, is it possible for you to create a mock app that mimicks what I am trying to do. Following is the sample sql statement which I am using:

Select t.column1,t.column2

from table t

where t.calendardate between (vStartDate) and (vEnddate)

and to narrow it down to simplest level let's assume I am makin a table that has Sales data for this Start Date

so the set analysis expression I am trying to use is : Sum ({<column_1={'value1'},calendardate ={"=date($(vStartDate))"}>} Sales)

Oh yes and an input box where i enter this date and reload.

Thanks a bunch!

Re: Use a variable declared in SQL into Qlikview charts

Anonymous — Fri, 06 Mar 2015 14:54:54 GMT

It is too much to ask...

But I just tried almost exactly the same in one of my current applications. The expression with the syntax you have in the opening question works fine:

sum({<Date={"$(VarDate)"}>} Price)

I have to notice that I have exactly the same date format for field Date and variable VarDate.

Re: Use a variable declared in SQL into Qlikview charts

Fri, 06 Mar 2015 15:11:01 GMT

I agree with you the problem lies with my date formats.

When I input the date in variable as '19-jan-2015', the date conversion : ={"=date($(vStartnDate))"} does not seem to work. But if I enter my date as 1/19/2015 12:00:00 AM this conversion seems to work.

Is there any other date conversion so that the user can still input the date in '19-feb-2015' format in the variable and the date conversion in set analysis does not get broken.

Thanks again!

Re: Use a variable declared in SQL into Qlikview charts

Anonymous — Fri, 06 Mar 2015 15:16:19 GMT

That's the major reason I asked you to upload the app, otherwise we can only guess.

Your last statement implies that your calendar date is in fact a timestamp, i.e. with hh:mm:ss. Comparing timestamps is not a good idea, they seldom match. I recommend to load al your dates as dates without time, e.g.

load

...

date(floor(datetimefield)) as datefield

...

from

Besides, it save application size.

Re: Use a variable declared in SQL into Qlikview charts

Fri, 06 Mar 2015 19:12:10 GMT

Using the following formula: sum({<calendardate={'=Date(vStarDate)'>} Sales).

Now I do get values but looks like its adding up data for both start Date and End date and displaying summed up values and not for separating them for respective dates. So what am suspecting is it when we put values under ''(quotes) it considers them as text value and not variables.

Because I have a text box where i do =date(vStartDate) (in the form of a formula) it displays expected date from the variable; but when I use this between quotes it does not recognize the individual date.

Re: Use a variable declared in SQL into Qlikview charts

Fri, 06 Mar 2015 21:16:07 GMT

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.