Date and Time Formatting - Request for Help?

[#1]

Hi all,

I was just wondering and hoping if someone in the community may be so kind as to help me out?  I have been more than struggling with these files that I'm using, and have tried most everything under the sun to normalize the dates and times so they can be related.  The main trouble that I have is that I do not have the luxury of a database, so the normalization of the fields is 100% left to mercy of Qlikview.

The Files: (attached) 

1st File - - an Actuals file (.csv) with only one Date Field

     True example data within the Date field (called UTC):  2/26/2015  9:00:00 AM

2nd File - - is a Forecast file (.xlsx)  with 3 fields (1 is short date, 1 is short time, 1 is datetimestamp)

     Exact Example of the Date Field:  02/26/15

     Exact Example of the Time Field:  9:00AM

     Exact Example of the Datetime Field:  2/26/2015 9:00 AM

These are my SET [format] statements in the Qliview script:

    > SET TimeFormat='hh:mm TT';

    > SET DateFormat='MM/DD/YYYY';

My guess:

I am thinking that the files, as they arrive in Qlikview, are not identified as dates and not identified as time fields?  I have tried 1,000 different iterations of functions to try to force field types across the 4 disparate fields (2 dates, 2 times) but to no avail.

     Date()

     Date#()

     MakeDate()

     Datetimestamp()

I have now resigned myself to try demarcate all of the digits within these provided fields, and re-compile the 2 date fields and the 2 time fields.  This is a horrible way to code, however.

Any help will be greatly appreciated!!

[robert_mika]

From what I see your csv file has text format where last 5 characters acting as time but they are not.

Maybe there is a better way but (as we all) I'm still learning.

Is this format you are getting from system (the csv) ?

You can post attached more data so I will see what can be done

[#3]

Thank you again, Robert.  I know your code should work for me, and it does from a formatting perspective (at least from what i can tell), but when it comes time for Qlikview to earn its due from an auto-relational aspect, it is failing me.

I'm trying to upload more data for you to view/use.

Thanks!

[#4]

Here is more data from the files that I am working with.  Thank you!

[#5]

Check out the file attached. It uses your new set, i have created a test field to illustrate it works.

Hope this helps!

RL

[#6]

see if this works!

RL

[#7]

Hi Rahul,

My apologies, but I only have the trial version of Qlikview right now.  Would you be willing to send along your script in a word doc?  Or copy/paste it into the post?

Thanks!

[#8]

Actual:

LOAD DisplaycName,

     cName,

     UTC,

     MakeDate(Right(left(UTC, Len(UTC)-6), len(left(UTC, Len(UTC)-6))-6),left(left(UTC, Len(UTC)-6), Len(left(UTC, Len(UTC)-6))-8),Left(Right(left(UTC, Len(UTC)-6), len(left(UTC, Len(UTC)-6))-3), len(Right(left(UTC, Len(UTC)-6), len(left(UTC, Len(UTC)-6))-3) )-5)) as Date1,

     MakeTime(Left(Right(UTC, Len(UTC)-10), Len(Right(UTC, Len(UTC)-10))-3),Right(Right(UTC, Len(UTC)-10), Len(Right(UTC, Len(UTC)-10))-4), 00) as Time1

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Forecast:

LOAD GroupDateTime,

     daystart(Date) as Date1,

     Date,

     Time(Time) as Time1,

     DateTime,

     1 as test

FROM

(ooxml, embedded labels, table is Export);

[robert_mika]

See below:

Directory;

LOAD DisplaycName,

     cName,

     Date#(makedate(TextBetween(UTC,'/',' ',2),TextBetween('/'&UTC,'/','/'),TextBetween(UTC,'/','/')),'MM/DD/YYYY')as Date,

     Timestamp (maketime(TextBetween(UTC,' ',':'),right(UTC,2)),'hh:mm TT') as Time,

     timestamp(Floor(makedate(TextBetween(UTC,'/',' ',2),TextBetween('/'&UTC,'/','/'),TextBetween(UTC,'/','/')))+Frac( maketime(TextBetween(UTC,' ',':'),right(UTC,2))),'YYYY-MM-DD hh:mm:ss TT') as DateTime,

     Actuals

FROM

File_Actuals.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Directory;

LOAD GroupDateTime,

     Date,

     Time,

     DateTime,

     Group,

     [Int VOL]

FROM

Forecast_File.xlsx

(ooxml, embedded labels, table is Export);

The final outcomes is Date , Time  DateTime as Keys

[#10]

So very very close!!  Thank you so much for your efforts!

As you will notice, in the Time visual, above, there are two entries for 12:30 AM.  That is, unfortunately, reflective of an entry for the Forecast File, and another entry for the Actuals file. 

Qlikview for some reason is not liking the fact that the types should now be understood by the application (through your awesome code), but is it possible that the formats are somehow still too diffferent (so Qlikview will not normalize them)?

Thank you again for your efforts, here!

[robert_mika · Accepted solution]

Never give up

Directory;

LOAD DisplaycName,

     cName,

     Date#(makedate(TextBetween(UTC,'/',' ',2),TextBetween('/'&UTC,'/','/'),TextBetween(UTC,'/','/')),'MM/DD/YYYY')as Date,

     Timestamp (maketime(TextBetween(UTC,' ',':'),right(UTC,2)),'hh:mm TT') as Time,

     timestamp(Floor(makedate(TextBetween(UTC,'/',' ',2),TextBetween('/'&UTC,'/','/'),TextBetween(UTC,'/','/')))

     +Frac( maketime(TextBetween(UTC,' ',':'),right(UTC,2))),'MM-DD-YYYY hh:mm:ss TT') as DateTime,

     Actuals

FROM

File_Actuals.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Directory;

LOAD GroupDateTime,

     Date(Date,'MM-DD-YYYY') as Date,

     time(time#(Time,'hh:mm TT'),'hh:mm TT') as Time ,

     DateTime,

     Group,

     [Int VOL]

FROM

Forecast_File.xlsx

(ooxml, embedded labels, table is Export);