Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     compare first rec to later record

Read-Only Read-Only Topic
Go
Search
Notify
Tools
compare first rec to later record
 Login/Join
 
Master
posted
I am working on a fex that is to compare the Recorded_Time of first record with the Recorded_Time
of the subsequent record where the Comment field contains the value 'ETT Re-taped'.

    Pt ID	PAT NAME	 Comment                    Value	Recorded_Time

4550315	SMITH, MABEL E	ETT Secured At	Lip L	11/30/2008 23:56:00
4550315	SMITH, MABEL E	ETT Taped at_(cm)	26	11/30/2008 23:56:00
4550315	SMITH, MABEL E	ETT Secured At	Lip L	12/01/2008 04:38:00
4550315	SMITH, MABEL E	ETT Taped at_(cm)	26	12/01/2008 04:38:00
4550315	SMITH, MABEL E	ETT Secured At	Lip L	12/01/2008 08:50:00
4550315	SMITH, MABEL E	ETT Taped at_(cm)	26	12/01/2008 08:50:00
4550315	SMITH, MABEL E	ETT Secured At	Lip L	12/01/2008 12:00:00
4550315	SMITH, MABEL E	ETT Taped at_(cm)	26	12/01/2008 12:00:00
4550315	SMITH, MABEL E	ETT Secured At	Lip L	12/01/2008 16:17:00
4550315	SMITH, MABEL E	ETT Taped at_(cm)	26	12/01/2008 16:17:00
4550315	SMITH, MABEL E	ETT Secured At	Lip L	12/01/2008 20:05:00
4550315	SMITH, MABEL E	ETT Taped at_(cm)	26	12/01/2008 20:05:00
4550315	SMITH, MABEL E	ETT Re-taped	Yes	12/01/2008 20:26:00



I have been playing with FST and LST but I am not getting very far.

Ideas anyone? Confused

Thanks,


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Guru
posted Hide Post
Is 'LAST' a possibility?

Sayed


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Report This Post
Virtuoso
posted Hide Post
What do you get when you do this?

TABLE FILE XXX
SUM FST.RECORDEDTIME 
LST.RECORDEDTIME
BY PT_ID
END




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Master
posted Hide Post
PT_ID PAT_NAME COMMENT VALUE RECORDED_TIME

4550315 SMITH, MABEL E ETT Secured At Lip L 11/30/2008 23:56:00
4550315 SMITH, MABEL E ETT Taped at_(cm) 26 11/30/2008 23:56:00
4550315 SMITH, MABEL E ETT Secured At Lip L 12/01/2008 04:38:00
4550315 SMITH, MABEL E ETT Taped at_(cm) 26 12/01/2008 04:38:00
4550315 SMITH, MABEL E ETT Secured At Lip L 12/01/2008 08:50:00
4550315 SMITH, MABEL E ETT Taped at_(cm) 26 12/01/2008 08:50:00
4550315 SMITH, MABEL E ETT Secured At Lip L 12/01/2008 12:00:00
4550315 SMITH, MABEL E ETT Taped at_(cm) 26 12/01/2008 12:00:00
4550315 SMITH, MABEL E ETT Secured At Lip L 12/01/2008 16:17:00
4550315 SMITH, MABEL E ETT Taped at_(cm) 26 12/01/2008 16:17:00
4550315 SMITH, MABEL E ETT Secured At Lip L 12/01/2008 20:05:00
4550315 SMITH, MABEL E ETT Taped at_(cm) 26 12/01/2008 20:05:00
4550315 SMITH, MABEL E ETT Re-taped Yes 12/01/2008 20:26:00
- - - - - - -
4550315 SMITH, MABEL E ETT Re-Repositioned Yes 12/05/2008 13:45:00
- - - - - - -
- - - - - - -
4550315 SMITH, MABEL E ETT Re-taped Yes 12/05/2008 13:45:00

I put all the first records for a patient into a hold file. Next, I put the
remaining records for the patient into a second hold file.

DEFINE FILE XXX
FIRST_REC/A1 MISSING ON = IF PT_ID NE LAST PT_ID THEN '1' ELSE MISSING;
END

TABLE FILE XXX
PRINT
PAT_NAME
COMMENT
VALUE
RECORDED_TIME AS RTIME
BY PT_ID SKIP-LINE
BY RECORDED_TIME NOPRINT
...
WHERE FIRST_REC EQ '1';
ON TABLE HOLD AS 1STREC
END
-RUN
-*
TABLE FILE XXX
PRINT
PAT_NAME
COMMENT
VALUE
RECORDED_TIME
BY PT_ID SKIP-LINE
BY RECORDED_TIME NOPRINT
...
WHERE FIRST_REC EQ MISSING;
ON TABLE HOLD AS RESTREC
END
-RUN
-*
JOIN CLEAR *
JOIN PT_ID IN 1STREC TO PT_ID IN RESTREC AS J1
-RUN
-*
DEFINE FILE 1STREC
RCD_TIME_DIFF/D12.2 = HDIFF(RECORDED_TIME,RTIME,'MINUTE','D12.2');
RCD_TIME_HRS/D12.2 = RCD_TIME_DIFF/60;
RTP_FLAG/A1 = IF PT_ID EQ LAST PT_ID AND FLO_DIS_NAME EQ 'ETT Re-taped' THEN '1' ELSE ' ';
WARN_FLAG/A1 = IF RTP_FLAG EQ '1' AND RCD_TIME_HRS GT 84 THEN '*****' ELSE ' ';
END
-*
-*
TABLE FILE 1STREC
PRINT

PAT_NAME
Comment
Value
Recorded_Time
BY PT_ID
END

So, I capture the recorded_time in the first record, and then compare this time to that
of a subsequent record where the the Comment field contains the value 'ETT Re-taped'.
Re: comparing first record:

4550315 SMITH, MABEL E ETT Secured At Lip L 11/30/2008 23:56:00

to the later record:
4550315 SMITH, MABEL E ETT Re-taped Yes 12/01/2008 20:26:00

The problem I am having now, is the next thing I want to do is compare

4550315 SMITH, MABEL E ETT Re-taped Yes 12/01/2008 20:26:00

to the next record where the the Comment field contains the value 'ETT Re-taped'.

4550315 SMITH, MABEL E ETT Re-taped Yes 12/05/2008 13:45:00

Any ideas? Razzer


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
<JG>
posted
Easy way is to READ the first time into a variable and do your compare against the variable.
 
Report This Post
Virtuoso
posted Hide Post
Would the following snippet of code be helpful?
TABLE FILE DATA
PRINT *
COMPUTE COMPARE/A20 = IF PAT_ID NE LAST PAT_ID THEN DATETIME
                 ELSE IF LAST COMMENT EQ 'ETT Re-taped' THEN LAST DATETIME ELSE LAST COMPARE;
END


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Platinum Member
posted Hide Post
Would a multi-verb request be suitable?

TABLE FILE CAR
SUM
FST.SEATS
COMPUTE FST_REC/I2=FST.SEATS;
BY CAR
PRINT SEATS
COMPUTE OTH_REC/I2=SEATS;
COMPUTE MYFIELD/A2=IF OTH_REC EQ FST_REC THEN 'Y' ELSE 'N';
BY CAR
END


WF 7.6.11
Output: HTML, PDF, Excel
 
Posts: 123 | Location: UK | Registered: October 09, 2003Report This Post
Guru
posted Hide Post
Try something like this to get the data
SUM
FST.RECORDED_TIME AS FIRST_TIME
COMPUTE LAST_time/MDYYMMSS = IF COMMENT CONTAINS 'ETT Re-taped' THEN RECORDED_TIME ;

THEN YOU CAN COMPARE THE 2 DATES.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Expert
posted Hide Post
Tom, I think this is what you want.
EX -LINES 8 EDAPUT MASTER,tmp_data,CV,FILE
FILENAME=TMP_DATA, SUFFIX=TABT,$
SEGNAME=TMP_DATA, $
  FIELD=PT_ID   ,ALIAS=  ,A7 ,A7 ,$
  FIELD=PAT_NAME,ALIAS=  ,A14,A14,$
  FIELD=COMMENT ,ALIAS=  ,A17,A17,$
  FIELD=VALUE   ,ALIAS=  ,A5 ,A5 ,$
  FIELD=REC_TIME,ALIAS=  ,HMDYYS,A19,$

EX -LINES 17 EDAPUT FOCTEMP,tmp_data,CV,FILE
Pt ID	PAT NAME	Comment	Value	Recorded_Time
4550315	SMITH, MABEL E	ETT Secured At	Lip L	11/30/2008 23:56:00
4550315	SMITH, MABEL E	ETT Taped at_(cm)	26	11/30/2008 23:56:00
4550315	SMITH, MABEL E	ETT Secured At	Lip L	12/01/2008 04:38:00
4550315	SMITH, MABEL E	ETT Taped at_(cm)	26	12/01/2008 04:38:00
4550315	SMITH, MABEL E	ETT Secured At	Lip L	12/01/2008 08:50:00
4550315	SMITH, MABEL E	ETT Taped at_(cm)	26	12/01/2008 08:50:00
4550315	SMITH, MABEL E	ETT Secured At	Lip L	12/01/2008 12:00:00
4550315	SMITH, MABEL E	ETT Taped at_(cm)	26	12/01/2008 12:00:00
4550315	SMITH, MABEL E	ETT Secured At	Lip L	12/01/2008 16:17:00
4550315	SMITH, MABEL E	ETT Taped at_(cm)	26	12/01/2008 16:17:00
4550315	SMITH, MABEL E	ETT Secured At	Lip L	12/01/2008 20:05:00
4550315	SMITH, MABEL E	ETT Taped at_(cm)	26	12/01/2008 20:05:00
4550315	SMITH, MABEL E	ETT Re-taped	Yes	12/01/2008 20:26:00
4550315	SMITH, MABEL E	ETT Re-taped	Yes	12/05/2008 20:26:00
4550315	SMITH, MABEL E	ETT Re-taped	Yes	12/09/2008 13:45:00


FILEDEF TMP_DATA DISK tmp_data.ftm

-RUN

TABLE   FILE TMP_DATA
 PRINT  
        PT_ID   
        PAT_NAME
        COMMENT 
        VALUE   
        REC_TIME
        COMPUTE
        CNTR/I9             = LAST CNTR + 1 ;
-*        FIRST_TS/HMDYYS     = IF CNTR EQ 1 THEN REC_TIME ELSE LAST FIRST_TS ;
        FIRST_TS/HMDYYS     = IF CNTR EQ 1
                              THEN REC_TIME
                              ELSE
                              IF LAST COMMENT EQ 'ETT Re-taped'
                              THEN LAST REC_TIME
                              ELSE LAST FIRST_TS ;
        RCD_TIME_DIFF/D12.2 = HDIFF(REC_TIME,FIRST_TS,'MINUTE','D12.2');
        RCD_TIME_HRS/D12.2  = RCD_TIME_DIFF/60;
        RTP_FLAG/A1         = IF PT_ID EQ LAST PT_ID AND COMMENT EQ 'ETT Re-taped' THEN '1' ELSE ' ';
        WARN_FLAG/A5        = IF RTP_FLAG EQ '1' AND RCD_TIME_HRS GT 84 THEN '*****' ELSE ' ';
END


You will have to add extra code to handle change of PT_ID.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Master
posted Hide Post
Thanks to everyone for your input.

Waz I have tested your ideas a bit, and
it looks like it works very well.

Good One


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Master
posted Hide Post
quote:
FILEDEF TMP_DATA DISK tmp_data.ftm

-RUN

TABLE FILE TMP_DATA
PRINT
PT_ID
PAT_NAME
COMMENT
VALUE
REC_TIME
COMPUTE
CNTR/I9 = LAST CNTR + 1 ;
-* FIRST_TS/HMDYYS = IF CNTR EQ 1 THEN REC_TIME ELSE LAST FIRST_TS ;
FIRST_TS/HMDYYS = IF CNTR EQ 1
THEN REC_TIME
ELSE
IF LAST COMMENT EQ 'ETT Re-taped'
THEN LAST REC_TIME
ELSE LAST FIRST_TS ;
RCD_TIME_DIFF/D12.2 = HDIFF(REC_TIME,FIRST_TS,'MINUTE','D12.2');
RCD_TIME_HRS/D12.2 = RCD_TIME_DIFF/60;
RTP_FLAG/A1 = IF PT_ID EQ LAST PT_ID AND COMMENT EQ 'ETT Re-taped' THEN '1' ELSE ' ';
WARN_FLAG/A5 = IF RTP_FLAG EQ '1' AND RCD_TIME_HRS GT 84 THEN '*****' ELSE ' ';
END


Here is the solution:

quote:
FILEDEF TMP_DATA DISK tmp_data.ftm
-RUN

TABLE FILE TMP_DATA
PRINT
PT_ID
PAT_NAME
COMMENT
VALUE
REC_TIME
-*
COMPUTE CNTR/I9 = IF PT_ID NE LAST PT_ID THEN 1 ELSE LAST CNTR + 1 ;
-*
FIRST_TS/HMDYYS = IF CNTR EQ 1
THEN REC_TIME
ELSE IF LAST COMMENT EQ 'ETT Re-taped'
THEN LAST REC_TIME
ELSE LAST FIRST_TS ;
RCD_TIME_DIFF/D12.2 = HDIFF(REC_TIME,FIRST_TS,'MINUTE','D12.2');
RCD_TIME_HRS/D12.2 = RCD_TIME_DIFF/60;
RTP_FLAG/A1 = IF PT_ID EQ LAST PT_ID AND COMMENT EQ 'ETT Re-taped' THEN '1' ELSE ' ';
WARN_FLAG/A5 = IF RTP_FLAG EQ '1' AND RCD_TIME_HRS GT 84 THEN '*****' ELSE ' ';
ON TABLE HOLD AS HOLD1
END
-RUN
-*
DEFINE FILE HOLD1
RTD/D12.2 MISSING ON = IF PT_ID EQ LAST PT_ID THEN RCD_TIME_DIFF ELSE MISSING;
END
-*
TABLE FILE HOLD1
" REPORT SAMPLE - TEST"
" "
PRINT
PT_ID AS 'PT ID'
PAT_NAME AS 'PT NAME'
COMMENT
VALUE
REC_TIME
CNTR
FIRST_TS AS 'REF TIME'
RTD AS 'RCD,TIME,DIF'
RCD_TIME_HRS AS 'RCD,TIME,HRS'
RTP_FLAG AS 'FLAG'
WARN
BY PT_ID SKIP-LINE NOPRINT
BY REC_TIME NOPRINT
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
STYLE=NORMAL,
RIGHTGAP=0.125000,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=DATA,
BACKCOLOR=(RGB(223 255 255) RGB(250 255 210)),
$
TYPE=DATA,
-* COLUMN=WARN_FLAG,
COLOR='RED',
STYLE=BOLD,
BACKCOLOR='WHITE',
WHEN=WARN EQ '*****',
$
ENDSTYLE
END
-RUN
-EXIT
Cool


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     compare first rec to later record

Copyright © 1996-2020 Information Builders