Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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

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.

1 Solution

Accepted Solutions
Employee
Employee

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

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

17 Replies
Employee
Employee

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

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
Contributor II

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

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

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

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

Employee
Employee

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

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

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

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


talk is cheap, supply exceeds demand
Not applicable

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

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

Not applicable

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

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

     

Date/Time of ED Patient ArrivalMCDateMCDayMCMonthMCTime
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);

Not applicable

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

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

Not applicable

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

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.

Community Browser