7 Replies Latest reply: Oct 22, 2015 9:15 AM by - - RSS

    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

        • Re: Using a variable in Set Analysis and script
          Gysbert Wassenaar

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

          • Re: Using a variable in Set Analysis and script
            Ruben Marin

            Hi, for me works with this:

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

            script: WHERE YEAR<=$(vMaxYear)

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

            • Re: Using a variable in Set Analysis and script
              Stefan Wühl

              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

                • 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.

                    • Re: Using a variable in Set Analysis and script
                      Stefan Wühl

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

                          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

                        • Re: Using a variable in Set Analysis and script
                          Ruben Marin

                          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.