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     Date comparison in Group By

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Date comparison in Group By
 Login/Join
 
Member
posted
I need to have a report that only shows duplicates by a Group of First and Last Name. If DOBs of entries fall within 2 years of each other within that group then display them, if not do not display them. Example code below.

TABLE FILE TEST
PRINT FIRST_NAME
LAST_NAME
DOB
BY FIRST_NAME
BY LAST_NAME
END

Example data:
Joe Smith 1/2/05
Joe Smith 3/6/06
Sally Black 12/1/02
Sally Black 4/5/07

I need to show the entries for Joe Smith b/c they are with 2 years of each other with the same first and last name. I do not need to show Sally Black b/c the entries are further than 2 years apart. Any help?


WebFocus 7.6.10
Data Migrator 7.6.10
Windows NT
Excel, PDF, HTML, AHTML
 
Posts: 11 | Registered: February 06, 2008Report This Post
Expert
posted Hide Post
That was quite a challenge!

FILEDEF MASTER DISK test1.mas
-RUN

-WRITE MASTER FILENAME=TEST1, SUFFIX=FIX
-WRITE MASTER SEGNAME=TEST1
-WRITE MASTER FIELDNAME=FIRST_NAME, FORMAT=A10, ACTUAL=A10, $
-WRITE MASTER FIELDNAME=LAST_NAME,  FORMAT=A10, ACTUAL=A10, $
-WRITE MASTER FIELDNAME=DOB,        FORMAT=DMYY, ACTUAL=A08, $
-RUN

FILEDEF TEST1 DISK test1.txt
-RUN

-WRITE TEST1 Joe       Smith     01021905
-WRITE TEST1 Joe       Smith     03061906
-WRITE TEST1 Sally     Black     12011902
-WRITE TEST1 Sally     Black     04051907

TABLE FILE TEST1
PRINT
COMPUTE FULLNAME/A21 = FIRST_NAME || (' ' | LAST_NAME);
BY FIRST_NAME NOPRINT
BY LAST_NAME NOPRINT
BY DOB
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN

DEFINE FILE H001
DOB_LAST/DMYY = LAST DOB;
DOB_DIFF/D4 = IF FULLNAME EQ LAST FULLNAME THEN DATEDIF(DOB_LAST, DOB, 'Y') ELSE 0;
END

TABLE FILE H001
PRINT 
DOB
BY FULLNAME
BY HIGHEST DOB_DIFF
ON TABLE HOLD AS H002 FORMAT ALPHA
END

DEFINE FILE H002
DOB_DIFF_LAST/D4 = LAST DOB_DIFF;
DOB_DIFF_NEW/D4  = IF FULLNAME EQ LAST FULLNAME THEN DOB_DIFF_LAST ELSE DOB_DIFF;

END

TABLE FILE H002
PRINT 
FULLNAME
DOB
DOB_DIFF
DOB_DIFF_LAST
DOB_DIFF_NEW
WHERE DOB_DIFF_NEW LE 2
END

The FILEDEF and WRITE statements are there to create a file like your example.

The first TABLE combines First and Last Name and sorts the data in the right order.

The first DEFINE calculates the difference in Years for people that have more than one record in the data file.

The second TABLE sorts the data by name and by highest DOB Difference - this is required by the next step. (The first row for every person will have 0 DOB Difference).

The second DEFINE calculates the same DOB Difference for each row for the person.

The third TABLE prints the data for people with DOB Difference <= 2.

I don't think there's a simpler way to do this - it sometimes takes multiple passes through the data, though, perhaps there's a way to do this with FRL.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
And working on Francis' code, this might work:
FILEDEF MASTER DISK test1.mas
-RUN

-WRITE MASTER FILENAME=TEST1, SUFFIX=FIX
-WRITE MASTER SEGNAME=TEST1
-WRITE MASTER FIELDNAME=FIRST_NAME, FORMAT=A10, ACTUAL=A10, $
-WRITE MASTER FIELDNAME=LAST_NAME,  FORMAT=A10, ACTUAL=A10, $
-WRITE MASTER FIELDNAME=DOB,        FORMAT=DMYY, ACTUAL=A08, $
-RUN

FILEDEF TEST1 DISK test1.txt
-RUN

-WRITE TEST1 Joe       Smith     01021905
-WRITE TEST1 Joe       Smith     03061906
-WRITE TEST1 Joe       Smith     03071907
-WRITE TEST1 Joe       Smith     03071908
-WRITE TEST1 Sally     Black     12011902
-WRITE TEST1 Sally     Black     04051907
SET PAGE=NOPAGE
DEFINE FILE TEST1
FULLNAME/A21 = FIRST_NAME || (' ' | LAST_NAME);
END
TABLE FILE TEST1
PRINT
COMPUTE DOB_DIFF/D4 = IF FULLNAME EQ LAST FULLNAME THEN DATEDIF(LAST DOB, DOB, 'Y') ELSE 0; NOPRINT
COMPUTE TRACK/A1 = IF DOB_DIFF LE 2 THEN 'Y' ELSE 'N'; NOPRINT
COMPUTE L_FULL/A21 = IF TRACK EQ 'Y' AND FULLNAME EQ LAST FULLNAME THEN LAST FULLNAME ELSE ' '; AS ''
COMPUTE L_DOB/DMYY = IF TRACK EQ 'Y' AND FULLNAME EQ LAST FULLNAME THEN LAST DOB ELSE 0; AS ''
OVER
FULLNAME AS '' DOB AS '' 
BY FULLNAME NOPRINT
BY DOB NOPRINT
WHERE TOTAL L_FULL NE ' ';
ON DOB SUBFOOT
" "
ON TABLE SET STYLE *
TYPE=REPORT,GRID=OFF,$
ENDSTYLE
END


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Member
posted Hide Post
Thanks Francis and Alan!!! Both of these worked so I just combined and edited for my fields. Thanks!!!


WebFocus 7.6.10
Data Migrator 7.6.10
Windows NT
Excel, PDF, HTML, AHTML
 
Posts: 11 | Registered: February 06, 2008Report 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     Date comparison in Group By

Copyright © 1996-2020 Information Builders