topic Re: Day & Month & Year in QlikView

Day & Month & Year

Wed, 23 Apr 2014 07:37:39 GMT

Hello all,

I've attached snapshot for order table and business intelligence plan. According to business intelligence plan how can we bring the data(Year & Month & Year) from orders date?

I request to help in this if anyone knows.

Regards,

Sattish G

Re: Day & Month & Year

Wed, 23 Apr 2014 07:44:29 GMT

LOAD Day(OrderDate) AS Day,

Month(OrderDate) AS Month,

Year(OrderDate) AS Year,

Date(OrderDate,'DDMMYYYY') AS Date_DDMMYYYY_Format
FROM Orders;

If you need Year Month Year use Year(OrderDate)&Month(OrderDate)&Year(OrderDate)

Re: Day & Month & Year

anbu1984 — Wed, 23 Apr 2014 07:46:16 GMT

Month(OrdersDate) gives you month in MMM. If you want months in number then use Num(Month(OrdersDate))

Date#(OrdersDate,'MMM-YYYY')

Re: Day & Month & Year

ashfaq_haseeb — Wed, 23 Apr 2014 07:48:47 GMT

Try the below.

Orders:

LOAD

CustomerID,

EmployeeID,

Freight,

OrderDate,

Year(OrderDate) AS Year,

Month(OrderDate) AS Month,

Day(OrderDate) AS Day,

OrderID,

ShipperID

;

SQL SELECT * FROM Orders;

Hope it help

Regards

ASHFAQ

Re: Day & Month & Year

tresB — Wed, 23 Apr 2014 07:50:15 GMT

If your source data is date type, you can directly use qv functions Day(), month() and year() like:

Load

Year(OrderDate) as Year,

Month(OrderDate) as Month,

Day(OrderDate) as Day,

WeekDay(OrderDate) as WeekDay

From <>;

Otherwise, use parsing function to get it in date format like:

Load

Year(Date#(OrderDate, 'DD-MMM-YY') as Year,

Month(Date#(OrderDate, 'DD-MMM-YY') as Month,

WeekDay(Date#(OrderDate, 'DD-MMM-YY') as WeekDay

From <>;

Re: Day & Month & Year

Wed, 23 Apr 2014 08:38:01 GMT

Thanks for answering.

And i would like to know about ODBC & OLEDB(datasources) definitions and as well difference.

How it works in real time?

When we go for ODBC and when we go for OLEDB?

Please explain in detail if anyone knows.

Re: Day & Month & Year

Wed, 23 Apr 2014 08:41:29 GMT

It depends on datasource, generally OLDEB quicker to extract but it is not always optimal. The data is realtime (at the time you reload the document) unless you are using direct discovery

Re: Day & Month & Year

MK_QSL — Wed, 23 Apr 2014 08:42:55 GMT

It's always better to use Master Calender for filling Dates and getting Year, Month, Quarter, YearMonth fields.

Check enclosed file ...

Re: Day & Month & Year

jagan — Wed, 23 Apr 2014 08:50:53 GMT

Hi,

Check this

ODBC provides access only to relational databases
OLE DB provides the following features
- Access to data regardless of its format or location
- Full access to ODBC data sources and ODBC drivers

oledb - what is the difference between OLE DB and ODBC data sources? - Stack Overflow

http://database.ittoolbox.com/documents/odbc-vs-oledb-18150

Regards,

Jagan.

Re: Day & Month & Year

Sat, 26 Apr 2014 12:03:06 GMT

Hi,

Everything is OK.But i can't get format of YearMonth field according to Business Intelligence Plan.Please help me in this.

Re: Day & Month & Year

tresB — Sat, 26 Apr 2014 12:08:24 GMT

Try like:

Load

Date(OrderDate, 'MMM-YYYY') as YearMonth

or, using a proper date function like:

Load

Monthname(OrderDate) as YearMonth

Re: Day & Month & Year

Sat, 26 Apr 2014 12:21:31 GMT

Thanks for quick response.