Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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

Tags (1)
1 Solution

Accepted Solutions
cheburashka
Contributor III

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

Hello Djalal,

Here is the full script.

Données:

LOAD action_id,

     action_group,

     dropdate,

     segmentdescription,

     ind_id,

     cl_18,

     amount,

     OP, 

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

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

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

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

     zipcode,

     city,

     language,

     sexcode,

     country,

     ind_status_flag,

     address_id,

     street_name,

     street_remainder,

     groupdescription,

     firstgift,

     lastgift

FROM

Données_DSC.xlsx

(ooxml, embedded labels, table is Feuil1);

,Koen

10 Replies
MVP
MVP

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
pgrenier
Contributor III

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

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
Contributor III

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

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

Not applicable

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

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
Contributor III

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

Have you added those lines within a load statement?

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

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.


talk is cheap, supply exceeds demand
Not applicable

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

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

Not applicable

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

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

MVP
MVP

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

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.)?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser