topic Re: Date and Time Formatting - Request for Help? in QlikView

Date and Time Formatting - Request for Help?

Fri, 13 Mar 2015 12:27:19 GMT

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!!

Re: Date and Time Formatting - Request for Help?

PrashantSangle — Fri, 13 Mar 2015 12:32:33 GMT

Hi,

Try for xls file

Date(date#(dateFieldName,'DD/MM/YY hh:mm TT'))

Timestamp(Timestamp#(dateFieldName,'DD/MM/YY hh:mm TT'))

and

for csv file try with

Date(date#(dateFieldName,'D/MM/YYYY hh:mm TT'))

Timestamp(Timestamp#(dateFieldName,'D/MM/YYYY hh:mm TT'))

Regards

Re: Date and Time Formatting - Request for Help?

Fri, 13 Mar 2015 12:56:29 GMT

Daystart(FieldName) as Date

Time(FieldName) as Time should do it.

Please see file attached.

Re: Date and Time Formatting - Request for Help?

robert_mika — Fri, 13 Mar 2015 13:04:21 GMT

Long winding

Load the xls file as normal and the csv as below:

Directory;

LOAD DisplaycName,

cName,

UTC,

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

FROM

file1_csv_example.csv

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

Directory;

LOAD GroupDateTime,

Date,

Time,

DateTime,

Column1,

Amt

FROM

file2_xlsx_example.xlsx

(ooxml, embedded labels, table is Export);

Where UTC2 is the csv file

Re: Date and Time Formatting - Request for Help?

Fri, 13 Mar 2015 14:32:05 GMT

Hi Robert, thank you so much for your reply!

It seems to be great for individual code, but when I run the script that combine the two files on the "Date" field and the "Time" field, here is result that I am ending up with. I feel like I am missing as step, or something?

Alternatively, do you think the maketime function you built, would also work for the excel file?

thanks!

Re: Date and Time Formatting - Request for Help?

Fri, 13 Mar 2015 14:34:09 GMT

Thank you very much for your reply, Rahul. Unfortunately, Qlikview still will not let these 4 fields of mine recognize each other with the same date format and time format.

Re: Date and Time Formatting - Request for Help?

Fri, 13 Mar 2015 14:37:31 GMT

Hi Max, thank you so much for your response. I'm still having issues getting the 4 fields to align. I need them to align on the same format, and the same field type:

Date field = a short date format = 'MM/DD/YYYY'

Time field = a short time format = 'HH:MM"

Thank you again!

Re: Date and Time Formatting - Request for Help?

Fri, 13 Mar 2015 14:39:24 GMT

Sorry, the Time field = a short time format = 'HH:MM TT'

Re: Date and Time Formatting - Request for Help?

robert_mika — Fri, 13 Mar 2015 14:40:22 GMT

You can post more data for the csv file maybe there are different formats or make formula for Excel file - so you will need to save the csv as xls.

Excel does not have makedate function but there are other ways to build a solution.

Let me know which way do you want to go

Re: Date and Time Formatting - Request for Help?

Fri, 13 Mar 2015 14:47:36 GMT

Ideally (I think), and for automation purposes, we would save the .xls file as a .csv? then, apply the logic for the date and time fields that you already sent in your original post?

I will try it both ways, and see what results.

Is there no simple function within Qlikview to just force a field into date format and field type? Thank you again!

Re: Date and Time Formatting - Request for Help?

robert_mika — Fri, 13 Mar 2015 14:56:53 GMT

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

Re: Date and Time Formatting - Request for Help?

Fri, 13 Mar 2015 15:15:06 GMT

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!

Re: Date and Time Formatting - Request for Help?

Fri, 13 Mar 2015 15:19:41 GMT

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

Re: Date and Time Formatting - Request for Help?

Fri, 13 Mar 2015 15:42:27 GMT

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

Hope this helps!

Re: Date and Time Formatting - Request for Help?

Fri, 13 Mar 2015 15:43:39 GMT

see if this works!

Re: Date and Time Formatting - Request for Help?

Fri, 13 Mar 2015 15:46:54 GMT

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!

Re: Date and Time Formatting - Request for Help?

Fri, 13 Mar 2015 15:48:11 GMT

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);

Re: Date and Time Formatting - Request for Help?

robert_mika — Fri, 13 Mar 2015 17:33:42 GMT

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

Re: Date and Time Formatting - Request for Help?

Fri, 13 Mar 2015 17:49:41 GMT

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!

Re: Date and Time Formatting - Request for Help?

robert_mika — Fri, 13 Mar 2015 20:19:23 GMT

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);