18 Replies Latest reply: Oct 6, 2015 8:20 AM by Shane Fenton RSS

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

    Shane Fenton

      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.