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     [SOLVED] Copy missing values from a latest available data

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Copy missing values from a latest available data
 Login/Join
 
Member
posted
I'm struggling to get the logic for below scenario. I will explain with a scenario.

I have data in the table as below. The table can have either more than one row for an year (different dates) or no entry for an year. I would like to create a hold file as mentioned below for the latest three years. If the table has more than one row for an year, need to consider the latest record(latest date). If you don't have record for an year, copy the count from earlier available year's participant count. Can somebody please advice how can I create the hold file.

Table A:

Date Participant_count
12/31/2014 70
08/30/2017 65
12/31/2017 80

HOLD File:

Year Count
2016 70
2017 80
2018 80

This message has been edited. Last edited by: FP Mod Chuck,
 
Posts: 17 | Location: columbus | Registered: August 01, 2018Report This Post
Virtuoso
posted Hide Post
Hi Jarves,
Filling in the gaps in the data was addressed some 20 years ago by Noreen Redden of FocWizard fame, using the MacGuyver technique. Here is an implementation.
  
-SET &ECHO=ALL;
-* File jarves01.fex
-* Parameters: start date, number of years, number of output years
-DEFAULT &START=2000, &YEARS=19, &OUTPUT=3
FILEDEF JARVES DISK JARVES.FTM
-RUN
-* Jarves data
-WRITE JARVES 12/31/2014 70
-WRITE JARVES 08/30/2017 65
-WRITE JARVES 12/31/2017 80
-CLOSE JARVES
-RUN
-* JARVES master file
EX -LINES 6 EDAPUT MASTER,JARVES,C,MEM
FILENAME=JARVES, SUFFIX=FIX
SEGNAME=JARVES, SEGTYPE=S0
FIELDNAME=JDM, USAGE=A6, ACTUAL=A6,$
FIELDNAME=JYEAR, USAGE=I4, ACTUAL=A4,$
FIELDNAME=PCOUNT, USAGE=I3, ACTUAL=A3,$
-RUN
-* Use MacGuyver technique to fill gaps between dates
JOIN BLANK WITH JYEAR IN JARVES TO BLANK IN FSEQ AS M_
DEFINE FILE JARVES
BLANK/A1 WITH JYEAR=' ';
BEGIN/I4=&START;
RYEAR/I4=BEGIN + COUNTER  -1;
RCOUNT/I3=IF RYEAR EQ JYEAR THEN PCOUNT ELSE 0;
END
-* Produce HOLD file with all the years
TABLE FILE JARVES
PRINT RCOUNT
BY HIGHEST RYEAR
IF COUNTER LE &YEARS
ON TABLE HOLD AS H1
END
-RUN
-* Get last value for each year
DEFINE FILE H1
TCOUNT/I3=IF RYEAR NE LAST RYEAR THEN RCOUNT ELSE IF RCOUNT NE 0 THEN RCOUNT ELSE LAST TCOUNT;
END
TABLE FILE H1
SUM LST.TCOUNT
BY RYEAR
ON TABLE HOLD AS H2
END
-RUN
-* Reduce HOLD file to last value
TABLE FILE H2
SUM COMPUTE LCOUNT/I3=IF TCOUNT NE 0 THEN TCOUNT ELSE LAST LCOUNT;
BY RYEAR
ON TABLE HOLD AS H3
END
-RUN
-* Output
TABLE FILE H3
PRINT LCOUNT
BY HIGHEST &OUTPUT RYEAR
END


MacGuyver FSEQ master (change the directory from FOCALPOINT to the name of the directory where fseq.txt is placed:
  
 FILE=FSEQ, SUFFIX=FIX, DATASET=FOCALPOINT/FSEQ.TXT, $
  SEGNAME=SEG1
   FIELD=CONTROL, BLANK , A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=CHAR, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$

MacGuyver data file, fseq.txt. IMPORTANT: the first character is a space!!!
  
 FSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVER
  


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Master
posted Hide Post
As with all thing FOCUS, and we use MacGyver a lot, we've got a permanent .FOC MacGyver file in baseapp. Here's the master and code to build the file.
FILE=MACGYVER,SUFFIX=FOC
SEGNAME=MAC1,SEGTYPE=S1,DATASET='/opt/ibi/apps/baseapp/macgyver.foc',$
  FIELD=BLANK,,A1,INDEX=I,$
SEGNAME=MAC2,SEGTYPE=S1,PARENT=MAC1
  FIELD=COUNTER,ORDER,I4,$


-* File loadmac.fex
-*
-* Build MACGYVER.FOC
-*
-DEFAULT &HOWMANY=600;
 CREATE FILE MACGYVER
 MODIFY FILE MACGYVER
 COMPUTE CTR/I9=;
 FIXFORM &HOWMANY(CTR/4 X-4)
 COMPUTE
   BLANK=' ';
   COUNTER=IF COUNTER EQ 0 THEN CTR ELSE COUNTER+1;
 MATCH BLANK
    ON MATCH CONTINUE
    ON NOMATCH INCLUDE
 MATCH COUNTER
    ON MATCH CONTINUE
    ON NOMATCH INCLUDE
 DATA
1
 END
-RUN


Just set &HOWMANY to the max number of rows needed in the MAC2 segment, and yes, we need 600 for one of our processes.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Virtuoso
posted Hide Post
Good One


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 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     [SOLVED] Copy missing values from a latest available data

Copyright © 1996-2020 Information Builders