I have a define I created to alter a date/time field to HhB (9 AM as an example). I am trying to get the count of records (In this case loans) for each hour of a given day. I have this define going Across and the report looks good except, it doesn't actually sum the values. Below is what I get.
8 AM 8 AM 8 AM

Conventional 1 . .
FHA 2 2 4

This should be
8 AM

Conventional 1
FHA 8
below is my code.

TABLE FILE INTRADAY
SUM
COMPUTE SubToGtCnt/D12 = CNT.INTRADAY.INTRADAY_PIPELINE.JACKET_NUM;
BY LOWEST INTRADAY.INTRADAY_PIPELINE.CA_NoCA
BY LOWEST INTRADAY.INTRADAY_PIPELINE.LOAN_TYPE
ACROSS LOWEST INTRADAY.INTRADAY_TRACKSTAT_1.SubToGtHour
WHERE ( INTRADAY.INTRADAY_PIPELINE.DIVISION EQ 'Call Center' ) AND ( INTRADAY.INTRADAY_PIPELINE.DOC_TYPE EQ 'Full' ) AND ( INTRADAY.INTRADAY_PIPELINE.PURPOSE_LOAN NE 'Purchase' ) AND (( INTRADAY.INTRADAY_TRACKING.XDESCRIPTION EQ 'Pre-gate Review' OR 'Initial Gate Setup' OR 'Initial Gate Review' ) AND ( INTRADAY.INTRADAY_TRACKSTAT_1.EVENT_DATE GE INTRADAY.CUSTOM_DATES.TODAY ));
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET XLSXPAGESETS ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
SUMMARY='Summary',
TITLETEXT='Summary',
$
ENDSTYLE
END

How do I get it to sum all by each hour instead of the way it shows now?

This message has been edited. Last edited by: FP Mod Chuck,

gsbatch1,
Looks like the data needs to be aggregated, so, use of a HOLD file may work.

See if this example works for you:

 
TABLE FILE INTRADAY
SUM 
COMPUTE SubToGtCnt/D12 = CNT.INTRADAY.INTRADAY_PIPELINE.JACKET_NUM;
BY LOWEST INTRADAY.INTRADAY_PIPELINE.CA_NoCA
BY LOWEST INTRADAY.INTRADAY_PIPELINE.LOAN_TYPE
BY LOWEST INTRADAY.INTRADAY_TRACKSTAT_1.SubToGtHour
WHERE ( INTRADAY.INTRADAY_PIPELINE.DIVISION EQ 'Call Center' ) 
  AND ( INTRADAY.INTRADAY_PIPELINE.DOC_TYPE EQ 'Full' ) AND ( INTRADAY.INTRADAY_PIPELINE.PURPOSE_LOAN NE 'Purchase' ) 
  AND (( INTRADAY.INTRADAY_TRACKING.XDESCRIPTION EQ 'Pre-gate Review' OR 'Initial Gate Setup' OR 'Initial Gate Review' ) 
  AND ( INTRADAY.INTRADAY_TRACKSTAT_1.EVENT_DATE GE INTRADAY.CUSTOM_DATES.TODAY ));
  ON TABLE HOLD AS HOLD1
END
-RUN

TABLE FILE HOLD1
SUM 
   SubToGtCnt
BY LOWEST CA_NoCA
BY LOWEST LOAN_TYPE
ACROSS LOWEST SubToGtHour

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET XLSXPAGESETS ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
SUMMARY='Summary',
TITLETEXT='Summary',
$
ENDSTYLE
END

OK. That worker perfectly. My new issue is I want to sort across by hour. 7AM, 8AM, 9AM, etc.
I cannot get this to work for the life of me.
I can get it to pull the hour out of the Date/Time field, but it's in 24 hour format.
HNAME(INTRADAY.INTRADAY_TRACKSTAT_1.EVENT_DATE, 'HOUR', 'hour').

I have not been able to find anything on converting a date/time to hour AM/PM.

Format "HhA"

(It's in Describing Data, date-time variables, time-only formats)

Unless I misunderstand, it might be a bit more complicated than simply reformatting a date-time column. I think the reformatted date-time column must be converted to alpha before grouping (SUM). Take a look at the results of the two TABLE FILEs:

The first report shows multiple values for the hour - because internally the un-reformatted date-time is used

The second report shows one row per hour, the hours are sorted correctly because they're first sorted with AM or PM, and the 12 before 01 is also taken care of.

-SET &ECHO=ON;

-*-- Create the master for TEST1 -------------------------------------
FILEDEF DATAMAST DISK TEST1.MAS
-RUN

-*--
-WRITE DATAMAST FILE=TEST1, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST FIELD=COUNTRY  , ALIAS=COUNTRY  , USAGE=A10   , ACTUAL=A10, $
-WRITE DATAMAST FIELD=         , ALIAS=FILL1    , USAGE=A01   , ACTUAL=A01, $
-WRITE DATAMAST FIELD=TEST_DTTM, ALIAS=TEST_DTTM, USAGE=HYYMDS, ACTUAL=A17, $

-*-- Create the data file for TEST1 ----------------------------------
FILEDEF TEST1 DISK test1.ftm
-RUN

-WRITE TEST1 ENGLAND    20171110130101200
-WRITE TEST1 ENGLAND    20171112175305000
-WRITE TEST1 ENGLAND    20171112195305000
-WRITE TEST1 CANADA     20171112175305000
-WRITE TEST1 CANADA     20171112083001000
-WRITE TEST1 CANADA     20171113083001000
-WRITE TEST1 CANADA     20171113172222000
-WRITE TEST1 USA        20171023001143000
-WRITE TEST1 USA        20171113172222000
-WRITE TEST1 CANADA     20160531035457000
-WRITE TEST1 CANADA     20171113120934000
-WRITE TEST1 CANADA     20171114140030000
-WRITE TEST1 S KOREA    20171114101030000
-WRITE TEST1 S KOREA    20171014011114000
-WRITE TEST1 W GERMANY  20171114101030000
-WRITE TEST1 S KOREA    20171116110956000
-WRITE TEST1 USA        20171114171030000
-WRITE TEST1 USA        20171116190956000

-*-- Create the report -----------------------------------------------
DEFINE FILE TEST1
THE_HOUR/HhA = TEST_DTTM;
THE_AMPM/A2  = SUBSTR(5, HCNVRT(TEST_DTTM, '(HHB)', 20, 'A20'), 4, 5, 2, 'A2');
THE_HOURA/A5 = HCNVRT(TEST_DTTM, '(HHB)', 5, 'A5');
SORT_HOUR/A5 = IF THE_HOURA EQ '12 AM' THEN '00 AM' ELSE IF THE_HOURA EQ '12 PM' THEN '00 PM' ELSE THE_HOURA;
END
-RUN

TABLE FILE TEST1
SUM
CNT.COUNTRY
BY THE_HOUR
END
-RUN

TABLE FILE TEST1
SUM
CNT.COUNTRY
BY THE_AMPM NOPRINT
BY SORT_HOUR NOPRINT
BY THE_HOURA
END
-RUN

TABLE FILE TEST1
PRINT
COUNTRY
TEST_DTTM
THE_AMPM
SORT_HOUR
THE_HOURA
END
-RUN

Is this overkill?

This message has been edited. Last edited by: Francis Mariani,

Agreed; but you can still use a datetime variable for the across, letting H formatting generate the [hh AM/PM] display. Define it as a datetime holding an arbitrary fixed date, and zero hours, minutes and seconds. Then set the Hour component based on the input value.

These DEFINEs should do it.

Hr/I2=DTPART( your_datetime , HOUR); -* isolate the hour part of the input datetime value
Hour_bucket/HhA=HINPUT(14,'19991231000000',8,Hour_bucket); -* Initialize to midnight of an arbitrary date
Hour_bucket=HSETPT(Hour_bucket,'HOUR',Hr,8,Hour_bucket); -* set the Hour component per the input datetime

This message has been edited. Last edited by: j.gross,

So, the 3 defines you listed, which order do I create them in and which one do I use as my final 'Across'?

@gsbatch1, this code can be put in a WebFOCUS procedure and executed.

-SET &ECHO=ON;

-*-- Create the master for TEST1 -------------------------------------
FILEDEF DATAMAST DISK TEST1.MAS
-RUN

-*--
-WRITE DATAMAST FILE=TEST1, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST FIELD=COUNTRY  , ALIAS=COUNTRY  , USAGE=A10   , ACTUAL=A10, $
-WRITE DATAMAST FIELD=         , ALIAS=FILL1    , USAGE=A01   , ACTUAL=A01, $
-WRITE DATAMAST FIELD=MEASURE1 , ALIAS=MEASURE1 , USAGE=P08C  , ACTUAL=A04, $
-WRITE DATAMAST FIELD=         , ALIAS=FILL1    , USAGE=A01   , ACTUAL=A01, $
-WRITE DATAMAST FIELD=TEST_DTTM, ALIAS=TEST_DTTM, USAGE=HYYMDS, ACTUAL=A17, $

-*-- Create the data file for TEST1 ----------------------------------
FILEDEF TEST1 DISK test1.ftm
-RUN

-WRITE TEST1 ENGLAND    0581 20171110130101200
-WRITE TEST1 ENGLAND    3348 20171112175305000
-WRITE TEST1 ENGLAND    8381 20171112195305000
-WRITE TEST1 CANADA     1223 20171112175305000
-WRITE TEST1 CANADA     6502 20171112083001000
-WRITE TEST1 CANADA     4054 20171113083001000
-WRITE TEST1 CANADA     2382 20171113172222000
-WRITE TEST1 USA        2349 20171023001143000
-WRITE TEST1 USA        2452 20171113172222000
-WRITE TEST1 CANADA     3452 20160531035457000
-WRITE TEST1 CANADA     1936 20171113120934000
-WRITE TEST1 CANADA     2331 20171114140030000
-WRITE TEST1 S KOREA    1341 20171114101030000
-WRITE TEST1 S KOREA    2344 20171014011114000
-WRITE TEST1 W GERMANY  1233 20171114101030000
-WRITE TEST1 S KOREA    0978 20171116110956000
-WRITE TEST1 USA        1698 20171114171030000
-WRITE TEST1 USA        0800 20171116190956000

-*-- Create the report -----------------------------------------------
DEFINE FILE TEST1
THE_AMPM/A2  = SUBSTR(5, HCNVRT(TEST_DTTM, '(HHB)', 20, 'A20'), 4, 5, 2, 'A2');
THE_HOURA/A5 = HCNVRT(TEST_DTTM, '(HHB)', 5, 'A5');
SORT_HOUR/A5 = IF THE_HOURA EQ '12 AM' THEN '00 AM' ELSE IF THE_HOURA EQ '12 PM' THEN '00 PM' ELSE THE_HOURA;
END
-RUN

TABLE FILE TEST1
SUM
MEASURE1

BY COUNTRY

ACROSS THE_AMPM NOPRINT
ACROSS SORT_HOUR NOPRINT
ACROSS THE_HOURA AS ''

ROW-TOTAL AS 'Total'

ON TABLE SUBTOTAL AS 'Total'
END
-RUN

This message has been edited. Last edited by: Francis Mariani,

I appreciate the code, but I (our company) just got WebFocus installed. We will not be allowing for the end users to access the code, and they will only have access to creating defines, computes, etc. I need to be able to produce the hour values through this way.

Did you mention this at the start of the thread? You need a solution that works in GUI tools only?

I've shown you an example with code. Someone will have to set up DEFINEs in the Master and the users will have to use the DEFINE columns.

I have no idea how you convey this to your users.

@Francis Mariani

Like I said, I appreciate your code. I apologize for mis leading, but I thought by originally posting just the define I was working with, I would get help with altering the define.

I will be a little more descriptive in the future.

Thanks for you help anyways.

GS,

When you are manipulating datetime values (and dates for that matter) within any prog. language, it is useful to understand how that datetime is held internally.

Like many other languages, FOCUS stores a datetime as a count of milliseconds since a base datetime - for FOCUS this is midnight 31st Dec 1900.

With that understanding, you then need to realise that a define is not actually changing the value, just how it is used.

For instance, just converting a DATETIME value via DATEHOUR/HhA = DATETIMEvalue; only renders the field within the report as the hour and meridian (e.g. 1AM), The underlying value hasn't been changed at all. So sorting by this value will show various values (for instance) of 1AM interspersed with other values. Why is this? The date portion of the DATETIME still remains, so you're sorting that not just the hour.

What about another function? Using a define of DATEHOUR/HhA = HHMS(DATETIMEvalue, 8, 'HhA'); should convert the DATETIME to just a TIME value. Great, not DATE portion to skew our values! But now you need to understand that you have only the TIME including minutes, seconds, milliseconds etc. So if you use this to sort, then you may (most likely) see many values of 7PM etc. because 19:00 is different to 19:02 etc.

So, you can see that what you really need is just the hour portion of the TIME value.

There are several ways of achieving this and each has it's own difficulties and advantages. It is really down to familiar methods / ease of remembering things .

This is all well and good, but you need to have this available to your end users easily and consistently.

My preferred method of achieving this would be to (as Francis alluded) include a define within the synonym or master or, if your data source is an RDBMS, an additional column in the source data or perhaps a view of the original source that includes an HOUR derivative.

To negate the need for your end users having to use a hidden sort value to get the correct order, I would suggess having a single define that they need to use, but with an HhA format.

T

Ok, building on Francis' example code and implementing what I suggest above, if you include a couple of DEFINEs within a synonym, you just need to tell your users to use teh "THE_HOUR" column within their report.

By adding the DEFINEs within the synonym, they would be available within the GUI list of fields for that synonym.

Because the value is actually a DATETIME, sorted values should be correct.

FILEDEF TEST1 DISK test1.ftm
EX -LINES * EDAPUT MASTER,test1,CV,FILE
 FILE=TEST1, SUFFIX=COM, $
 SEGNAME=SEG1, SEGTYPE=S0, $
 FIELD=COUNTRY  , ALIAS=COUNTRY  , USAGE=A10   , ACTUAL=A10, $
 FIELD=MEASURE1 , ALIAS=MEASURE1 , USAGE=P08C  , ACTUAL=A04, $
 FIELD=TEST_DTTM, ALIAS=TEST_DTTM, USAGE=HYYMDS, ACTUAL=A17, $
$ This define just obtains the HOUR as a number
 DEFINE TMP_HOUR/I2 = HPART(TEST_DTTM, 'HOUR', 'I2'); $
$ This define first sets the datetime to midnight for the date.
$ it then removes the date, so we effectively have a time for midnight.
 DEFINE TMP_TIME/HhA = HHMS(HMIDNT(TEST_DTTM, 8, 'HhA'), 8, 'HhA'); $
$ This function inserts a time component for us - in our case the hour.
$ So we end up with exactly what we want, the hour in isolation!
 DEFINE THE_HOUR/HhA = HSETPT(TMP_TIME, 'HOUR', TMP_HOUR, 8, 'HhA'); $
EDAPUT*
-RUN

EX -LINES * EDAPUT FOCTEMP,test1,CV,FILE
ENGLAND,0581,20171110130101200,$
ENGLAND,3348,20171112175305000,$
ENGLAND,8381,20171112195305000,$
CANADA,1223,20171112175305000,$
CANADA,6502,20171112083001000,$
CANADA,4054,20171113083001000,$
CANADA,2382,20171113172222000,$
USA,2349,20171023001143000,$
USA,2452,20171113172222000,$
CANADA,3452,20170531035457000,$
CANADA,1936,20171113120934000,$
CANADA,2331,20171114140030000,$
S KOREA,1341,20171114101030000,$
S KOREA,2344,20171014011114000,$
W GERMANY,1233,20171114101030000,$
S KOREA,0978,20171116110956000,$
USA,1698,20171114171030000,$
USA,0800,20171116190956000,$
EDAPUT*
-RUN

TABLE FILE TEST1
SUM MEASURE1
BY COUNTRY
ACROSS THE_HOUR AS ''
ROW-TOTAL AS 'Total'
ON TABLE SUBTOTAL AS 'Total'
END
-RUN

T

There is also a way to "hide" the temporary columns that are used to build the final column for the users to select, THE_HOUR, just forget what it is?

...,ACCESS_PROPERTY=(INTERNAL),$

works for fields; not sure about DEFINEs.

Another alternative is to give the users a Business View wrapper that presents only the fields you want them to see.

There are a couple of methods for achieving THE_HOUR using just one define. However, I would be inclined to check parsed SQL to ensure that it is being passed to an RDBMS query (as I would with the previous example)!!!

DEFINE THE_HOUR/HhA = HMASK(TEST_DTTM, 'H', DT(00:00), 8, 'HhA'); $

or

DEFINE THE_HOUR/HhA = HEXTR(TEST_DTTM, 'H', 8, 'HhA'); $

T