topic Re: Extract Year in QlikView

Extract Year

engishfaque — Tue, 10 Mar 2015 23:35:39 GMT

Dear All,

Please help me to extract date, month and year from the listed below data,

I have two formats of data/time in my sales file, where from i have to extract Date, Month, and Year. Each values should be separate as I can assign to variables.

Mar 9, 2015 12:00:40 AM PDT

Mar 9, 2015 12:00:40 AM PST

Example file is also attached, please find.

Kind regards,

Ishfaque Ahmed

Re: Extract Year

engishfaque — Wed, 11 Mar 2015 02:43:43 GMT

Any update?

Re: Extract Year

anbu1984 — Wed, 11 Mar 2015 03:03:21 GMT

LOAD Year(Date#(Left([date/time],23),'MMM DD, YYYY hh:mm:ss TT'))

FROM

[TestFile.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Re: Extract Year

jyothish8807 — Wed, 11 Mar 2015 03:33:06 GMT

Hi Ishfaque,

Try like this:

Mar 9, 2015 12:00:40 AM PDT here Replace F1 with your field.

Load

left(F1,3) as Month,

replace(subfield(F1,' ',2),',','') as Date,

mid(F1,7,5) as Year

resident Tab1;

Regards

Re: Extract Year

mukesh24 — Wed, 11 Mar 2015 04:15:38 GMT

Hi,

first convert u r data into date format by using date() or date#() then apply Year() ,month() to generate date.

Regards,

Mukesh

Re: Extract Year

robert_mika — Wed, 11 Mar 2015 04:25:41 GMT

Directory;

LOAD left([date/time],3) as Month,

TextBetween([date/time],' ',',') as Day,

TextBetween([date/time],' ',' ',2) as Year

FROM

TestFile.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Re: Extract Year

robert_mika — Wed, 11 Mar 2015 04:38:13 GMT

Jyothish

Your expression for Year is not dynamic.

It will fail on

Apr 25, 2015 12:18:54 AM PDT

or row where the Day has two characters.

Re: Extract Year

jyothish8807 — Wed, 11 Mar 2015 04:53:29 GMT

Right my bad. Thanks for highlighting it.

Regards

Re: Extract Year

Wed, 11 Mar 2015 07:04:47 GMT

Hi Ishfaque,

Try this type of code in scripting and then you will get first "Mar 9,2015" after that you can get separately day,month and year.

Dates:

LOAD

@1,

Left(FileName(),12) AS Dates

FROM

[Comunity\Mar 9, 2015 120040 AM PDT.txt]

(txt, codepage is 1252, no labels, delimiter is '\t', msq);