topic Getting Month and Year Fields from a Single Date Field in QlikView

Getting Month and Year Fields from a Single Date Field

Mon, 08 Jun 2009 22:50:10 GMT

Hi everyone I just started learning Qlikview for an internship. Right now I have an Excel spreadsheet with a Column of dates (OrderDate). However, in my Qlikview application I need to make selections based on Month and Year.

Currently, in my Month and Year tables, I'm using the expressions Month(OrderDate) and Year(OrderDate) respectively. This works, but I also have a Current Selections box in my application and since neither the Month or the Year are fields, making a selection in either of the boxes only shows up as an OrderDate Field with a Value of somthing like 32 of 548.

I have a completed example as a reference and in that example, making a selection in either the Month or Year boxes shows up as the Month or Year field in the Current Selections box, and has a value of Jan, Feb, ...etc and 2005,2006,...etc.

Any help would be appreciated.

Thanks, Yiling

Getting Month and Year Fields from a Single Date Field

Mon, 08 Jun 2009 22:55:14 GMT

Hello Yiling

You should probably create these fields in your load script, at the moment you probably have something like:

LOAD OrderDate FROM xxxxx.XLS

You should change this so that you have:

LOAD OrderDate, MONTH(OrderDate, 'MMM') AS OrderMonth, YEAR(OrderDate,'YYYY') AS OrderYear FROM xxxx.XLS

This should make them separate fields.

Good luck,

Nigel.

Getting Month and Year Fields from a Single Date Field

Mon, 08 Jun 2009 23:05:26 GMT

Hi Nigel,

Thanks for the quick reply.

Currently my script looks like:

ODBC CONNECT TO [Excel Files;DBQ=C:\...\Sales.xls];
SQL SELECT *
FROM `C:\...\Sales.xls`.`Customers$`;

SQL SELECT *
FROM `C:\...\Sales.xls`.`Orders$`;

where Customers and Orders are two different tables within the Sales.xls document. I know that the SQL Select * means its reading all the fields from the table, but I never use LOAD anywhere. How would I change this?

Thanks again,

Yiling

Getting Month and Year Fields from a Single Date Field

Mon, 08 Jun 2009 23:24:51 GMT

In that case you might want to simply change your script to read something like

SQL SELECT *, Month(OrderDate,'MMM') As OrderMonth, Year(OrderDate,'YYYY') AS OrderYear FROM...........

Regards,

Nigel.

Getting Month and Year Fields from a Single Date Field

Mon, 08 Jun 2009 23:34:43 GMT

Worked like a charm!

Thanks!

Getting Month and Year Fields from a Single Date Field

Mon, 08 Jun 2009 23:38:41 GMT

Just one little thing though.

The Month function only takes one argument so you can't load it and format it all at once.

But thanks again!

Yiling