Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Oh, and taking into account Jonathan's suggestion above to explicitely define the date format will also greatly help
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?
Have you added those lines within a load statement?
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.
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
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
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.)?