Solved: Date and Time Formatting - Request for Help? - Qlik Community

Report Inappropriate Content · ‎2015-03-13

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 · ‎2015-03-13

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

PrashantSangle · ‎2015-03-13

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 🙂

Report Inappropriate Content · ‎2015-03-13

Daystart(FieldName) as Date

Time(FieldName) as Time should do it.

Please see file attached.

RL

robert_mika · ‎2015-03-13

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

Report Inappropriate Content · ‎2015-03-13

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!

Report Inappropriate Content · ‎2015-03-13

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.

Report Inappropriate Content · ‎2015-03-13

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!

Report Inappropriate Content · ‎2015-03-13

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

robert_mika · ‎2015-03-13

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

Report Inappropriate Content · ‎2015-03-13

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!