Day, Hour as separate fields from MM/DD/YYYY HH:mm:ss?

[#1]

I have been searching this board for awhile and tried a variety of functions to extract day name and hour field from a csv file. The date is in the following format: 8/31/2015 22:05 but I've had a really hard time figuring out how to get dates formatted for use in graphs and list boxes easily. Even after reading through an HIC post on Dates/Times, and countless other threads. I'm trying to figure out what I'm missing.

I've tried the following:

date(ceil([Date/Time of ED Patient Arrival])) as Date,

Floor([Date/Time of ED Patient Arrival]) as Date,

Date(Timestamp#([Date/Time of ED Patient Arrival],'MM/DD/YYYY h:mm:ss TT'),'MM/DD/YYYY') as DateTest,

Timestamp#([Date/Time of ED Patient Arrival], 'YYYY-MM-DD') as DateArrivalTest,

Day(SubField([Date/Time of ED Patient Arrival],' ',1)) as DateDayTest,

Day(DatePartTest) as DayDateArrival

Finally this worked to get Date/Time separated:

subfield([Date/Time of ED Patient Arrival],' ',1) as Date,

subfield([Date/Time of ED Patient Arrival],' ',2) as Time,

And this worked to get the HH:

Hour(subfield([Date/Time of ED Patient Arrival],' ',2)) as TimeHH

But now I've not been able to get the Day name from a date. I have tried the following:

Day(subfield([Date/Time of ED Patient Arrival],' ',1)) as DateDay, //not working

DayName(subfield([Date/Time of ED Patient Arrival],' ',1)) as DateDayName //not working

Any suggestions? I'm coming from Business Objects where FormatDate() was easy and consistent, and am a bit lost with how Qlikview handles formatting vs actual value. I think I understand how it saves two values, and that formatting doesn't necessarily change the numeric value, but some of these simple changes which I need have been unbelievably frustrating. Does the source file format actually matter? My next step is just going to be editing and changing the column in the csv to several formats to test and understand it I guess.

[hic]

No, you shouldn't need to edit your csv:s.

It's just two steps:

1. Interpreting the date/timestamp
2. Formatting or transforming

If your format is 8/31/2015 22:05  then the interpretation step should be fixed by:

   Load Timestamp#([Date/Time of ED Patient Arrival],'M/D/YYYY hh:mm') as XXX,

Note that I removed the seconds and the AM/PM format in the format code.

Check that this works before you do anything else. If it works, this field should be right aligned in a list box and it should be possible to format it as a number on the numbers tab in the properties sheet.

Once this is done, you can wrap this field in additional functions. Example

Load *,

     Day(XXX) as Day

     Month(XXX) as Month;

Load

     Timestamp#([Date/Time of ED Patient Arrival],'MM/DD/YYYY hh:mm TT') as XXX

     From Source (...)

HIC

[malini_qlikview]

If your source date format is 8/31/2015 22:05, try the below

Date(Date#('8/31/2015 22:05','MM/DD/YYYY hh:mm'))  as Date,

Time(Date#('8/31/2015 22:05','MM/DD/YYYY hh:mm'))  as Time,

Day(Date#('8/31/2015 22:05','MM/DD/YYYY hh:mm'))  as Day,

Month(Date#('8/31/2015 22:05','MM/DD/YYYY hh:mm'))  as Month

You can replace the hard coded date '8/31/2015 22:05' in the above with your date field

[Peter_Cammaert]

date#([Date/Time of ED Patient Arrival], 'MM/DD/YYYY hh:mm') AS DateTime

should work to convert the spec from the csv into a DateTime dual() value in QlikView. Then (in the next load) you can use things like

Date(DateTime, 'WWW') AS WeekdayNameShort

Date(DateTime, 'WWWW') AS WeekdayNameLong

and

Hour(DateTime) AS Hour

to get the values you want. The input format doesn't really matter as QlikView is quite capable in interpreting formatted values like this. But you have to learn QlikView what to expect in your csv first.

Peter

[Clever_Anjos]

Is it possible to post a sample of your csv file?

[Gysbert_Wassenaar]

You can use the floor and frac functions:

date(floor(date#([Date/Time of ED Patient Arrival],'MM/DD/YYYY h:mm:ss TT')),'MM/DD/YYYY') as Date

time(frac(date#([Date/Time of ED Patient Arrival],'MM/DD/YYYY h:mm:ss TT')),'hh:mm:ss') as Time

You can show the individual parts by modifying the format string:

time(frac([Date/Time of ED Patient Arrival]),'h') will show the hours

time(frac([Date/Time of ED Patient Arrival]),'m') will show the minutes

time(frac([Date/Time of ED Patient Arrival]),'s') will show the seconds

[#7]

Well first I have to say I'm sort of honored to get a reply so quickly not only from everyone else, but HIC as well!

I tried Timestamp#([Date/Time of ED Patient Arrival],'M/D/YYYY hh:mm') as HICDateTime but it came back as left aligned 2015/08/31 22:05:00 in a listbox, which from the rest of your post I assume means that something isn't correct in either my source csv, or my script.

I've stripped down the csv and my script to just this date field in order to figure it out before working on anything else.

My test script is as follows (removed some of filepath for post):

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LOAD

  [Date/Time of ED Patient Arrival],

  Timestamp#([Date/Time of ED Patient Arrival],'M/D/YYYY hh:mm') as HICDateTime

FROM

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

[#8]

Unfortunately none of these worked either. All return dash ( - ) with nothing in each field.

Date/Time of ED Patient Arrival	MCDate	MCDay	MCMonth	MCTime
2015/08/01 00:05:00

Script as follows:

LOAD

  [Date/Time of ED Patient Arrival],

  Date(Date#([Date/Time of ED Patient Arrival],'MM/DD/YYYY hh:mm')) as MCDate,

  Time(Date#([Date/Time of ED Patient Arrival],'MM/DD/YYYY hh:mm')) as MCTime,

  Day(Date#([Date/Time of ED Patient Arrival],'MM/DD/YYYY hh:mm')) as MCDay,

  Month(Date#([Date/Time of ED Patient Arrival],'MM/DD/YYYY hh:mm')) as MCMonth

FROM

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

[#9]

Unfortunately this returned nothing different than the normal [Date/Time of ED Patient Arrival] field.

LOAD

  [Date/Time of ED Patient Arrival],

  Date#([Date/Time of ED Patient Arrival],'MM/DD/YYYY hh:mm') as PCDateTime

FROM

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

[#10]

Yes.

I also just noticed what is slightly more confusing to me is that the raw date is displayed as M/DD/YYYY hh:mm in excel, but the cell value in the formula field in excel is down to seconds and in AM/PM. When imported to Qlikview however from csv, it displays as YYYY/MM/DD hh:mm:ss.

I'm posting here - but think I'm going to redo my source extract and see if there is something in the csv formatting that is throwing things off.