Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Furiku
Contributor
Contributor

Extracting year and month from date field

Hello! I'm having issues extracting date from date field stored in .txt

Sample data: 

StoreID,Date,Division,Units,Revenue
340051,22/08/2006,Eyewear,5,250
340051,22/08/2006,Jewelry,28,616
340051,22/08/2006,Luggage,2,350
340051,22/08/2006,Shoes,2,300
340052,22/08/2006,Handbags,3,585

 

using SET DateFormat='D.M.YYYY'; as my dateformat.

Been trying to extract it with Month(Date) as Month, and Year(Date) as Year, without them working. 

How should I go around to get this to work?

4 Replies
rubenmarin

Hi, the Date#() function is used to set format of the date being read, ie:

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

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

...

Brett_Bleess
Former Employee
Former Employee

Joakim, did Ruben's post get things working for you?  If so, do not forget to come back and use the Accept as Solution button on his post to give him credit and let others know the suggestion worked.  If you are still working upon things, leave an update for us.

Here is a Design Blog link with some further info regarding date functions too:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Date-Function/ba-p/1463157

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
florentina_doga
Partner - Creator III
Partner - Creator III

use makedate in script for data

create a new field like

makedate(subfield(Date,'/',-1),subfield(Date,'/',2),subfield(Date,'/',1)) as Date_new

and in preload

year(Date_new) as year

month(Date_new) as month

florentina_doga
Partner - Creator III
Partner - Creator III

use this script

aa: load *,
year(Date_new) as year,
month(Date_new) as month;
load *,
makedate(subfield(Date,'/',-1),subfield(Date,'/',2),subfield(Date,'/',1)) as Date_new;
load * inline [StoreID,Date,Division,Units,Revenue
340051,22/08/2006,Eyewear,5,250
340051,22/08/2006,Jewelry,28,616
340051,22/08/2006,Luggage,2,350
340051,22/08/2006,Shoes,2,300
340052,22/08/2006,Handbags,3,585];