Convert date format

[rdsuperlike]

I am trying to convert the date from CY2014-01Jan to  1/1/2014 in qlikview. I used date function but didnt get any results

I am picking up CY2014-01Jan from excel load.

Any inputs?

[hic]

Are the dates left-aligned? If so, they are text (i.e. not interpreted as dates), and you need to use the Date#()-function to force QlikView to understand that these are dates.

See more on Get the Dates Right

HIC

[engishfaque · Accepted solution]

Dear Rdsuperlike,

Kindly find attached document.

Table1:

LOAD date(date#(SubField(YourDateFieldName, 'CY'), 'YYYY-DDMMM'),'DD/MM/YYYY') as DateFormat

FROM

DateSample.xlsx

(ooxml, embedded labels, table is Sheet1);

Kind regards,

Ishfaque Ahmed

[jpapador]

In your load script:

MakeDate(Mid(DateField, 3, 4), Mid(DateField, 8, 2), 1) as NewDateField

[MarcoWedel]

Date(Date#(Mid(datefield,3),'YYYY-DDMMM'),'M/D/YYYY')

[MarcoWedel]

Could be

Date(Date#(Mid(datefield,3),'YYYY-DDMMM'),'D/M/YYYY')

as well.

[rdsuperlike]

Thanks!

I also wanted month, year and day as a seperate field along with this date conversion.

I tried using

Load

date(date#(SubField(Date, 'CY'), 'YYYY-DDMMM'),'DD/MM/YYYY') as Date,

     Month(Date) as Month,

     Day(Date) as Day,

     Year(Date) as Year

But this didnt work. Ny help is appreciated

[engishfaque]

Dear Rdsuperlike,

Kindly find attached document.

In attached document, everything is separated from the full date, example: (FullDate, Year, Month, Date).

Kind regards,

Ishfaque Ahmed

[hic]

@ ria

You cannot have the definition of the Date field in the same Load as you use the Date. You need to use a Preceding Load:

Load *,

     Month(Date) as Month,

     Day(Date) as Day,

     Year(Date) as Year;

Load

     date(date#(SubField(Date, 'CY'), 'YYYY-DDMMM'),'DD/MM/YYYY') as Date,

     ...

See more on Preceding Load

HIC

[rdsuperlike]

Thanks!      yeah thats what I tried doing yesterday and it worked!