topic Re: Convert mixed text dates to Date in QlikView

Convert mixed text dates to Date

johngouws — Mon, 18 Sep 2017 05:14:23 GMT

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;

Re: Convert mixed text dates to Date

tresB — Mon, 18 Sep 2017 05:20:24 GMT

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

Re: Convert mixed text dates to Date

antoniotiman — Mon, 18 Sep 2017 05:22:38 GMT

In Script

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

Regards,

Antonio

Re: Convert mixed text dates to Date

johngouws — Mon, 18 Sep 2017 05:26:15 GMT

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

Re: Convert mixed text dates to Date

vishalmanu — Mon, 18 Sep 2017 05:30:22 GMT

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

Re: Convert mixed text dates to Date

tresB — Mon, 18 Sep 2017 05:30:29 GMT

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

Re: Convert mixed text dates to Date

johngouws — Mon, 18 Sep 2017 05:33:30 GMT

Hello Antonia:

Sometimes the easiest solves the issue.

Thank you