topic Re: Formatting a Mixed Datetime Field in QlikView

Formatting a Mixed Datetime Field

haymarketpaul — Tue, 09 Feb 2016 14:15:23 GMT

I've got a column with a mix of datetimes in it like this and i just want to format the column to leave me with DD/MM/YYYY

10/15/2015 10:16 AM

10/02/2015 22:24

09/25/2015 03:13 PM

10/06/2015 11:42

09/27/2015 12:26 PM

10/17/2015 09:03 PM

10/01/2015 15:15

10/18/2015 12:07 AM

10/06/2015 08:08

10/12/2015 15:13

10/09/2015 18:27

08/24/2015 12:23 PM

10/20/2015 10:12 AM

10/14/2015 01:10 PM

Any ideas much appreciated

Re: Formatting a Mixed Datetime Field

sunny_talwar — Tue, 09 Feb 2016 14:24:27 GMT

May be try this with Alt function:

For TimeStamp

TimeStamp(Alt(Num(TimeStampField), Num(TimeStamp#(TimeStampField, 'MM/DD/YYYY hh:mm TT')), Num(TimeStamp#(TimeStampField, 'MM/DD/YYYY hh:mm')))) as TimeStampField

For Date

Date(Floor(Alt(Num(TimeStampField), Num(TimeStamp#(TimeStampField, 'MM/DD/YYYY hh:mm TT')), Num(TimeStamp#(TimeStampField, 'MM/DD/YYYY hh:mm'))))) as DateField

Sample Script:

Table:

LOAD *,

Date(Floor(TimeStampField)) as DateField;

LOAD TimeStamp(Alt(Num(TimeStampField),

Num(TimeStamp#(TimeStampField, 'MM/DD/YYYY hh:mm TT')),

Num(TimeStamp#(TimeStampField, 'MM/DD/YYYY hh:mm')))) as TimeStampField

Inline [

TimeStampField

10/15/2015 10:16 AM

10/02/2015 22:24

09/25/2015 03:13 PM

10/06/2015 11:42

09/27/2015 12:26 PM

10/17/2015 09:03 PM

10/01/2015 15:15

10/18/2015 12:07 AM

10/06/2015 08:08

10/12/2015 15:13

10/09/2015 18:27

08/24/2015 12:23 PM

10/20/2015 10:12 AM

10/14/2015 01:10 PM

];

Re: Formatting a Mixed Datetime Field

antoniotiman — Tue, 09 Feb 2016 14:29:25 GMT

Hi Paul,

try

Date(Date#(PurgeChar(YourDateField,'AMP'),'MM/DD/YYYY hh:mm'),'DD/MM/YYYY' )

or SubField(YourDate,' ',1)

Regards,

Antonio

Re: Formatting a Mixed Datetime Field

Mark_Little — Tue, 09 Feb 2016 14:33:37 GMT

Hi Paul,

If it is the case that the one ending am or PM are formatted MM/DD/YYYY and the other are DD/MM/YYYY

then maybe something like below.

DATETemp:

LOAD * INLINE [

Date

10/15/2015 10:16 AM

10/02/2015 22:24

09/25/2015 03:13 PM

10/06/2015 11:42

09/27/2015 12:26 PM

10/17/2015 09:03 PM

10/01/2015 15:15

10/18/2015 12:07 AM

10/06/2015 08:08

10/12/2015 15:13

10/09/2015 18:27

08/24/2015 12:23 PM

10/20/2015 10:12 AM

10/14/2015 01:10 PM

];

NoConcatenate

LOad

Date,

IF(Right(Date,1) = 'M',

DATE(DATE#(LEFT(Date,10),'MM/DD/YYYY'),'DD/MM/YYYY'),

DATE(LEFT(Date,10),'DD/MM/YYYY')

) as DATEFormated

Resident DATETemp;

Drop Table DATETemp;

Mark

Re: Formatting a Mixed Datetime Field

haymarketpaul — Tue, 09 Feb 2016 14:35:44 GMT

Sorry it's not clear from my sample data

both formats are MM/DD/YYYY

Re: Formatting a Mixed Datetime Field

Mark_Little — Tue, 09 Feb 2016 14:38:25 GMT

just need

LEFT(Date,10),'MM/DD/YYYY') as Date

Mark

Re: Formatting a Mixed Datetime Field

haymarketpaul — Tue, 09 Feb 2016 14:54:25 GMT

Thanks for reply

I need the output in DD/MM/YYYY so tried this...

Date(Date#(Left([Opt In Date],10),'MM/DD/YYYY'),'DD/MM/YYYY')

but the dates without the AM/PM parts seem to be missing from my output ?

I've attached a snapshot of the xlsx file i've got

Any ideas?

Re: Formatting a Mixed Datetime Field

sunny_talwar — Tue, 09 Feb 2016 14:57:26 GMT

Try this:

TimeStamp(

Alt(

Num(TimeStampField),

Num(TimeStamp#(TimeStampField, 'MM/DD/YYYY hh:mm TT')),

Num(TimeStamp#(TimeStampField, 'MM/DD/YYYY hh:mm'))

)

, 'DD/MM/YYYY hh:mm:ss TT') as TimeStampField

Re: Formatting a Mixed Datetime Field

Mark_Little — Tue, 09 Feb 2016 15:00:35 GMT

HI,

That seems fine on my data,

Maybe try adding a trim to removed any spaces?

Date(Date#(Left(Trim([Opt In Date]),10),'MM/DD/YYYY'),'DD/MM/YYYY')

Mark

Re: Formatting a Mixed Datetime Field

haymarketpaul — Tue, 09 Feb 2016 15:23:41 GMT

Thanks for reply - still can't get the dates without the AMPM parts to show? The field only gives me this (with Sunny's answer as well)...

24/08/2015

25/09/2015

27/09/2015

28/09/2015

29/09/2015

30/09/2015

14/10/2015

15/10/2015

17/10/2015

18/10/2015

20/10/2015

22/10/2015

Thought it might be something to do with the xlsx which is data that comes out of Silverpop ?

If i do this...

Date#(Left(Trim([Opt In Date]),10),'MM/DD/YYYY')

I get this...

42014.6354

42045.9333

42165.3388

42165.4875

42257.7687

42287.525

42348.6340

08/24/2015

09/25/2015

09/27/2015

09/28/2015

09/29/2015

09/30/2015

10/14/2015

10/15/2015

10/17/2015

10/18/2015

10/20/2015

10/22/2015

If i add then add Date(Date#(Left(Trim([Opt In Date]),10),'MM/DD/YYYY'),'DD/MM/YYYY')

i lose all the dates that look like numbers eg 42014.6354

Perhaps i need to convert these somehow.

Re: Formatting a Mixed Datetime Field

haymarketpaul — Tue, 09 Feb 2016 15:31:21 GMT

That's close except the dates without the AMPM part are still in MM/DD/YYYY format...

TimeStamp(

Alt(

Num([Opt In Date]),

Num(TimeStamp#([Opt In Date], 'MM/DD/YYYY hh:mm TT')),

Num(TimeStamp#([Opt In Date], 'MM/DD/YYYY hh:mm'))

), 'DD/MM/YYYY')

ie...

10/01/2015

10/02/2015

10/06/2015

24/08/2015

10/09/2015

25/09/2015

27/09/2015

28/09/2015

29/09/2015

30/09/2015

10/10/2015

14/10/2015

15/10/2015

17/10/2015

18/10/2015

20/10/2015

22/10/2015

10/12/2015

Re: Formatting a Mixed Datetime Field

Mark_Little — Tue, 09 Feb 2016 15:35:44 GMT

Strange formatting problem?

try

IF(RIGHT([Opt In Date],1) = 'M',

Date(Date#(Left(TRIM([Opt In Date]),10),'MM/DD/YYYY'),'DD/MM/YYYY'),

Date(Date(Left(TRIM([Opt In Date]),10),'MM/DD/YYYY'),'DD/MM/YYYY')) as DATE

Re: Formatting a Mixed Datetime Field

haymarketpaul — Tue, 09 Feb 2016 15:43:47 GMT

No cigar i'm afraid...

10/01/2015

10/02/2015

10/06/2015

24/08/2015

10/09/2015

25/09/2015

27/09/2015

28/09/2015

29/09/2015

30/09/2015

10/10/2015

14/10/2015

15/10/2015

17/10/2015

18/10/2015

20/10/2015

22/10/2015

10/12/2015

Going off to chat with the lovely young lady who sends me the spreadsheet