Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Using a variable in Set Analysis and script

Hello

I'm having a problem using the same variable in the script and Set Analysis.

For example I want to load all the years, months and days from a predefined date QVD until the end of the current year. To do so I defined the following variable via Variable Overview (without outer quotes):

vMaxYear: "=text(Date(today(), 'YYYY'))"

To define it that way has the benefit of seeing the calculated value in an input box and especially it works in a Set Analysis like in a title string (without outer quotes):

"=MinString({1<YEAR={"=$(vMaxYear)"}>} DAY&'.'&MONTH&'.'&YEAR)"

Yet it does not work in the script:

Date:

LOAD 

    YEAR, MONTH, DAY

FROM [..\shared\qvd\DATE.QVD](qvd)

WHERE YEAR<=$(vMaxYear);

Until now I tried many combinations of double/single quotes, equal signs and using/leaving dollar expansion but none of them worked in both application fields with only one definition of the variable. Yet I came to the result that leaving the = in the definition the script runs well but the Set Analysis does not.

For now I prefer the definition with = since I can see the value in an input box but it just won't work in the script. Is there a solution for both using only a single variable?

Thanks in advance

1 Solution

Accepted Solutions
MVP
MVP

Re: Using a variable in Set Analysis and script

Yes, define your variable in the script using LET, i.e.

Let vTest2 = Text(Date(Today(),'YYYY'));

or as Gybert suggested using Year() function.

If you define it in the variable overview, the Dollar sign expansion in the script will contain the equal sign, leading to

WHERE YEAR <= =Text(Date(Today(),'YYYY'))

-->Syntax invalid

7 Replies

Re: Using a variable in Set Analysis and script

Try creating vMaxYear as Year(Today()). You're now comparing numeric YEAR values with a text value.


talk is cheap, supply exceeds demand

Re: Using a variable in Set Analysis and script

Hi, for me works with this:

variable: LET vMaxYear = Year(Today());

script: WHERE YEAR<=$(vMaxYear)

expression: Sum({<Year={$(vMaxYear)}>} Amount)

MVP
MVP

Re: Using a variable in Set Analysis and script

Yes, define your variable in the script using LET, i.e.

Let vTest2 = Text(Date(Today(),'YYYY'));

or as Gybert suggested using Year() function.

If you define it in the variable overview, the Dollar sign expansion in the script will contain the equal sign, leading to

WHERE YEAR <= =Text(Date(Today(),'YYYY'))

-->Syntax invalid

Not applicable

Re: Using a variable in Set Analysis and script

Alright, that does the trick

Using let the values are created during script execution time but that shouldn't be a problem in my current case. Sadly there's only one answer to mark as the right one. Ruben got me on the right track at first but yours is more precisely.

I also saw the invalid syntax but hoped for another mechanism which unlike dollar expansion would evaluate the function at first.

Gysbert mentioned the Year() function and this one works well but sometimes it is important how the data are formatted and date() gives a better handling imho - combined with text() or num() it should even return the right data type.

MVP
MVP

Re: Using a variable in Set Analysis and script

Dollar sign expansion will anyway expand the variable to plain constant number in the script code before parsing, so no need to worry about right data types.

The Magic of Dollar Expansions

Data Types in QlikView

I would go for just using (as suggested above)

LET vMaxYear = Year(Today() );

It's just much easier to understand.

Re: Using a variable in Set Analysis and script

Hi, If you do LET vMaxYear = Date(Today(), 'YYYY'); --> QV stores the string 2015 and the value 22/10/2015

If you create a text box with expression "=AddMonths(vAño, 3)" --> returns 22/01/2016

So be aware of what value you're really saving and if you go with the date version consider to use EndYear(), EndMonth(), Today()... as it will change the value you're storing (it will show all as '2015').

If you use LET vMaxYear = Text(Date(Today(), 'YYYY')); --> Then you're storing a text, not a date, and date functions like AddMonths() will fail.

Not applicable

Re: Using a variable in Set Analysis and script

Well I don't want to fight about it but in my case vMaxYear might be used in comparing combined fields of year and month so it's neccessary to have a 4 char year and a 2 char month - using text(date(today(),'MM')) it is save to have leading 0s for string operations whilst set analysis does not care.

Just my two cents - maybe I'll see the perks of year(), month(),.. in the future

Community Browser