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.

[#2]

I restarted my own analysis using the same small sample csv attached above. When importing the csv, Day(), Month(), Hour() don't work. When saving that same csv as an xlsx file, then importing, all functions appear to work correctly...

[hic · Accepted solution]

If it comes back as a left-aligned '2015/08/31 22:05:00' then you use the wrong format code. You should use 'YYYY/MM/DD hh:mm:ss' instead.

HIC

[Clever_Anjos]

Please test

LOAD

  date(Floor(timestamp)) as date,

  time(Frac(timestamp)) as hour;

LOAD

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

FROM

[https://community.qlik.com/servlet/JiveServlet/download/882930-189835/Qlikview%20ED%20Date%20Time%20...]

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

[#5]

This worked, but I'm really confused as to why - the preceding load of what is essentially a variable that hasn't been loaded yet? If a script loads left to right, top to bottom how does the load of XXX work essentially before XXX has been created?

[hic]

The preceding load is not executed top to bottom. See more on

Preceding Load

HIC

[Peter_Cammaert]

Yes of course it did. That's because the QlikView field will keep the text format, but the underlying value will now be a real datetime value. And that value allows you to extract anything you want, like day name and hour (as your original post stated).

[#8]

This was also correct - though apparently I can only mark 1 response as correct. Again though, as I mentioned in HIC response I'm having difficulty for some reason understanding how I can process variables in a preceding load that haven't been created. Any reference, suggestions for understanding this better are greatly appreciated. If it's something that you just have to accept with Qlikview - I guess I can do that too!

Thank you again hic, cleveranjos, gwassenaar, and others - this is actually my first post after fighting with this for several hours a day and searching the boards repeatedly - I'm blown away at the active engagement of these boards compared to some other BI tools and look forward to hopefully contributing myself someday as I learn QV.

[Peter_Cammaert]

I made an example QVW document that reads your example csv file using nothing more than the code I posted earlier on. This is the result:

As you can see, the leftmost column is your original data that is still left-aligned. Meaning that it is recognized only as text and not as a DateTime value. With the date#() function, these values are converted into field DateTime (right-aligned) and from there on simple function calls (Date(), Hour() Minute() etc.) will extract the other values. See the (very simple) load script for the actual code.

Peter

[Edit] Sorry, forgot to include DateTime as a key field. Fixed now, included correct image.