Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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.

4 Replies
MVP
MVP

Re: month function

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?

Not applicable

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.

nilesh_gangurde
Valued Contributor

Re: month function

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

MVP
MVP

Re: month function

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;

Community Browser