4 Replies Latest reply: Aug 13, 2012 4:56 AM by Stefan Wühl RSS

    month function

      I am new to qlikview.... want to get month name in script but...

      As per help month function writes back month name such as Jan,Feb... but what i found out if use it in script it returns month number.

        • Re: month function
          Stefan Wühl

          month() function returns a dual, so a text representation ('Jan',Feb',...) as well as a numerical representation (1,2,3...). You can force QV to return one or the other by using text() resp. num() function.

           

          Could you describe you problem a bit closer, e.g by by posting a snippet of your load script?

            • Re: month function

              This is part of script

               

              SQL SELECT CustomerID,

                  EmployeeID,

                  OrderDate,

                  OrderID,

                  Datename(month,OrderDate) As OrdMnthNm,

                  month(OrderDate) As OrdMnth,

                  year(OrderDate) AS OrdYear

              FROM Northwind.dbo.Orders;

               

              The Datename Sql function returns full month name but then sorting gives problem. The month qlikview function expected to return Jan,Feb but writes month number.

                • Re: month function
                  Nilesh Gangurde

                  I will suggest you to write the script as follows,

                   

                  TEMP:

                  SQL SELECT CustomerID,

                      EmployeeID,

                      OrderDate,

                      OrderID

                  FROM Northwind.dbo.Orders;

                   

                  DATA:
                  Laod *,

                          Date(OrderDate) as Date,

                          Day(OrderDate) as Day,

                          Month(OrderDate) as Month,

                          Year(OrderDate) as Year

                  Resident TEMP;

                   

                  Drop table TEMP;

                   

                  This Will work....

                   

                  Regards,

                  Nilesh Gangurde

                  • Re: month function
                    Stefan Wühl

                    joships123 schrieb:

                     

                    This is part of script

                     

                    SQL SELECT CustomerID,

                        EmployeeID,

                        OrderDate,

                        OrderID,

                        Datename(month,OrderDate) As OrdMnthNm,

                        month(OrderDate) As OrdMnth,

                        year(OrderDate) AS OrdYear

                    FROM Northwind.dbo.Orders;

                     

                    The Datename Sql function returns full month name but then sorting gives problem. The month qlikview function expected to return Jan,Feb but writes month number.

                    Bottom line: If you call month() function in a SQL SELECT, you call the SQL function (as with datename), no QV functions are known within the SQL SELECT part of your script. You can use QV month() or any other QV functions only outside the SQL SELECT, e.g. in a preceding LOAD or a resident LOAD.

                    Preceding LOAD:

                     

                    LOAD *,

                        month(OrderDate) as OrdMnth;  // calling the QV month function

                    SQL SELECT CustomerID,

                        EmployeeID,

                        OrderDate,

                        OrderID,

                        Datename(month,OrderDate) As OrdMnthNm, // calling SQL function

                        year(OrderDate) AS OrdYear                          // calling SQL function

                    FROM Northwind.dbo.Orders;