How to extract the year, month and date from a date function in order to make a graph.

[#1]

Hello everyone,

I've just sarted using qlikview, I think my question is really basic yet I cannot manage to do it. I have loaded my datas on Qlikview,

one of the field I have in my data is named pdate, its format is dd/mm/yyyy. I want to extract the year, month and day from this field. I need it to make basic bar charts where the variables are year and amount.

Thank you in advance for your answers.

Djalal

[jonathandienst]

Hi

This will do it:

     Date(Date#(pdate, 'DD/MM/YYYY')) As pdate,

     Year(Date#(pdate, 'DD/MM/YYYY')) As Year,

     Month(Date#(pdate, 'DD/MM/YYYY')) As Month,

     Day(Date#(pdate, 'DD/MM/YYYY')) As Day,

Regards

Jonathan

[pgrenier]

Good day Djalal,

In your load statement, simply add a few extra columns with calls on functions Month(), Year(), etc. You may find that there are a lot of very helfful QlikView functions that can be used by typing F1 (for help), and looking for the section called "Date and Time Functions".

For example,

TableBeingLoaded:

LOAD datefield, Year(datefield) as yearfield, Month(datefield) as monthfield

From [datafile];

Hope this helps, regards,

Philippe

[pgrenier]

Oh, and taking into account Jonathan's suggestion above to explicitely define the date format will also greatly help

[#5]

Thank you both for your answers!

Jonathan, I just copied the script you wrote, and it gave an error:

"

Unknown intrsuction

Date(Date#(pdate, 'DD/MM/YYYY')) as pdate,

Year(Date#(pdate, 'DD/MM/YYYY')) as Year,

Month(Date#(pdate, 'DD/MM/YYYY')) as Month,

Day(Date#(pdate, 'DD/MM/YYYY')) as Day,"

Is there something else I need to add before or after maybe?

[pgrenier]

Have you added those lines within a load statement?

[Gysbert_Wassenaar]

And if you want to make things a lot easier for yourself you can use the Qlikview Components library to create a calendar for you from your date field instead of coding it all yourself. Download the qvc-8.1.zip file and look at the CalendarFromField.qvw document in the Examples directory to see how to use it.

[#8]

Ok, I've just done it.I put "Load pdate," before the whole expression 

I reloaded the file now, it showed no error, however I cannot manage to find the new fields, Do you if I missed a step?

Thanks again for your answers

[#9]

Hi guys,

In the end, I could not manage to extract the year and the month. Here is what I put in the script right after the load of my data field.

LOAD pdate,

Date(Date#(pdate, 'DD/MM/YYYY')) As pdate,

Year(Date#(pdate, 'DD/MM/YYYY')) As Year,

Month(Date#(pdate, 'DD/MM/YYYY')) As Month,

Day(Date#(pdate, 'DD/MM/YYYY')) As Day,

Do I missed a step, or are there things I did not write correctly?

Regards,

Djalal

[jonathandienst]

Have you included a data source (From <file> or Resident <table>, where <file> is a file path name or database table name and <table> is a resident table name respectively.)?