Extracting year and month from date field

[Furiku]

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?

[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]

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

[florentina_doga]

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]

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];