Solved: How to extract the year, month and date from a dat... - Qlik Community

Report Inappropriate Content · ‎2013-09-13

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

cheburashka · ‎2013-09-16

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

View solution in original post

jonathandienst · ‎2013-09-13

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 · ‎2013-09-13

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 · ‎2013-09-13

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

Report Inappropriate Content · ‎2013-09-13

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 · ‎2013-09-13

Have you added those lines within a load statement?

Gysbert_Wassenaar · ‎2013-09-13

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

Report Inappropriate Content · ‎2013-09-13

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

Report Inappropriate Content · ‎2013-09-16

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 · ‎2013-09-16

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

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