Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Convert mixed text dates to Date

Hello.

I have a need to show rates per day but have come across a stumbling block with the date outputs I am receiving.

The date reads M/D/Y but the lengths between the '/' vary. For example, there can are either 1 or 2 values for the month and the same applies to the date. The only constant is the Year: Currently I am doing the below, but I think there must be a more efficient solution.

Any ideas how I could accomplish this will be appreciated.

tmp:

Load * INLINE [

RowNo, Date, Ex_Rate

1, 9/18/2017, 14

2, 12/9/2017, 13.5] ;

NoConcatenate

tmp2:

Load

RowNo,

If(Index(Date, '/', 1) =2,

     Left(Date,1), 

     Left(Date,2) ) as Day,

If(Index(Date, '/', 1) =2,

     Mid(Date,3,2), 

     Mid(Date,4,1) ) as Month,

Right(Date,4) as Year,

Date,

Ex_Rate

Resident tmp;

Drop Table tmp;

1 Solution

Accepted Solutions
Highlighted
Master III
Master III

In Script

SET DateFormat='M/D//YYYY';

Regards,

Antonio

View solution in original post

6 Replies
Highlighted
MVP
MVP

How can you be sure if 12/9 is 12th Sep or 9th Dec?

Highlighted
Master III
Master III

In Script

SET DateFormat='M/D//YYYY';

Regards,

Antonio

View solution in original post

Highlighted
Partner
Partner

If I look at RowNo 1, the 18th has to be a Date. There after I have to trust the constant format.

Highlighted
Creator
Creator

Also you can try converting multiple date formats into single format using Alt function and date#.

Highlighted
MVP
MVP

That means you are getting the format from first row. If so, what if your first row was like: 10/12/2017 ?

Highlighted
Partner
Partner

Hello Antonia:

Sometimes the easiest solves the issue.

Thank you