Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rdsuperlike
Creator
Creator

Convert date format

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?

1 Solution

Accepted Solutions
engishfaque
Specialist III
Specialist III

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

View solution in original post

9 Replies
hic
Former Employee
Former Employee

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
Specialist III
Specialist III

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
Partner - Specialist
Partner - Specialist

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
Creator
Creator
Author

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
Specialist III
Specialist III

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
Former Employee
Former Employee

@ 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
Creator
Creator
Author

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