topic Re: Extracting year and month from date field in QlikView

Extracting year and month from date field

Furiku — Sun, 20 Oct 2019 15:22:21 GMT

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?

Re: Extracting year and month from date field

rubenmarin — Sun, 20 Oct 2019 17:27:21 GMT

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,

...

Re: Extracting year and month from date field

Brett_Bleess — Tue, 29 Oct 2019 12:12:13 GMT

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

Re: Extracting year and month from date field

florentina_doga — Tue, 29 Oct 2019 13:45:18 GMT

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

Re: Extracting year and month from date field

florentina_doga — Tue, 29 Oct 2019 13:49:59 GMT

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