topic Re: Date Format Change in QlikView

Date Format Change

Tue, 21 Oct 2014 02:53:10 GMT

“How to change the date (eg. Excel source field - 2 Sep 2014) to 09/2014 format using Qlik view?”

Re: Date Format Change

jyothish8807 — Tue, 21 Oct 2014 03:01:46 GMT

Hi Flavian,

Try this:

Date(Date#(YourField,'DD MMM YYYY'),'MM/YYYY') as New Date

Regards

Re: Date Format Change

jagan — Tue, 21 Oct 2014 03:37:48 GMT

Hi,

Try like this

LOAD

Date(Date#(DateFieldName, 'DD MMM YYYY'), 'MM/YYYY') AS NewDateField

FROM DataSource;

LOAD

Date(DateFieldName, 'MM/YYYY') AS NewDateField

FROM DataSource;

Note : Replace DateFieldName with your actual field name.

Hope this helps you.

Regards,

jagan.

Re: Date Format Change

CELAMBARASAN — Tue, 21 Oct 2014 03:54:10 GMT

Hope QlikView automatically take it as Date format if realy formally the format is date in excel, You only need to convert it as to different format as needed.

Load *,

Date(MonthStart(FieldName), 'MM/YYYY') AS MonthYear

From Excel;

or if it is formatted as text in excel then use

Load *,

Date(MonthStart(Date#(FieldName, 'DD MMM YYYY')), 'MM/YYYY') AS MonthYear

From Excel;

Re: Date Format Change

Tue, 21 Oct 2014 04:23:33 GMT

Sorry, I am a beginner of QV. Can you edit how date conversion should be for the following script. Want to change the 'Incident Date' to a new date.

Service,
     [Type Of Employment],
     ID,
     [Incident Date],
     Age,
     Gender,
     SAC,
     [Incident Type],
     Surname,
     [Type of Worker],
     Incident,

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Date Format Change

CELAMBARASAN — Tue, 21 Oct 2014 04:30:56 GMT

Assuming that this is part of a load statement, since LOAD keyword is missing

LOAD

Service,
     [Type Of Employment],
     ID,
     [Incident Date],

Date(MonthStart([Incident Date]), 'MM/YYYY') AS [Incident MonthYear],

//or use Date(MonthStart(Date#([Incident Date], 'DD MMM YYYY')), 'MM/YYYY') AS [Incident MonthYear],
     Age,
     Gender,
     SAC,
     [Incident Type],
     Surname,
     [Type of Worker],
     Incident
FROM

(ooxml, embedded labels, table is Sheet1);

Re: Date Format Change

jyothish8807 — Tue, 21 Oct 2014 04:33:18 GMT

Hi Flavian,

Try this:

Table1:

load

Service,
[Type Of Employment],
ID,

Date(Date#([Incident Date],'DD MMM YYYY'),'MM/YYYY') as New Date,

    Age,
     Gender,
     SAC,
     [Incident Type],
     Surname,
     [Type of Worker],
     Incident,

FROM

(ooxml, embedded labels, table is Sheet1);

Regards

Re: Date Format Change

Tue, 21 Oct 2014 04:43:01 GMT

When I reload with that statement, I get a error like below;

Unknown statement

Service,

ID,

Date(MonthStart(), 'MM/YYYY') AS ,

Age,

Gender,

SAC,

Surname,

Incident

FROM

(ooxml, embedded labels, table is Sheet1)

Re: Date Format Change

PrashantSangle — Tue, 21 Oct 2014 04:52:39 GMT

Hi,

Try this,

Load

     Service,
     [Type Of Employment],
     ID,

[Incident Date],
Date([Incident Date],'MM/YYYY') as NewIncidentDate,

//If above statement Wont work then try below commented statement.

//Date(Date#([Incident Date],'DD MMM YYYY'),'MM/YYYY') as NewIncidentDate,

     Age,
     Gender,
     SAC,
     [Incident Type],
     Surname,
     [Type of Worker],
     Incident,

FROM

(ooxml, embedded labels, table is Sheet1);

reGARDS

Re: Date Format Change

Tue, 21 Oct 2014 05:22:37 GMT

Date(Date#(,'DD MMM YYYY'),'MM/YYYY') as NewIncidentDate, worked.

Thanks

Re: Date Format Change

jagan — Tue, 21 Oct 2014 05:25:25 GMT

Hi,

You need to give column name like below

Date(Date#([Incident Date],'DD MMM YYYY'),'MM/YYYY') as NewIncidentDate

Hope this helps you.

Regards,

Jagan.

Re: Date Format Change

PrashantSangle — Tue, 21 Oct 2014 05:37:35 GMT

Hi,

If you get correct output then close the thread by selecting appropriate reply as correct answer.

Regards