Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date and Time Formatting - Request for Help?

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

1 Solution

Accepted Solutions
robert_mika
Master III
Master III

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

View solution in original post

19 Replies
PrashantSangle

Hi,

Try for xls file

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

or

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

and

for csv file try with

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

or

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable
Author

Daystart(FieldName) as Date

Time(FieldName) as Time should do it.

Please see file attached.

RL

robert_mika
Master III
Master III

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

Not applicable
Author

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!

exampleoutput.JPG

Not applicable
Author

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.

Not applicable
Author

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!

Not applicable
Author

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

robert_mika
Master III
Master III

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

Not applicable
Author

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!