Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Copy missing values from a latest available data
Go
New
Search
Notify
Tools
Reply
  
[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: 14 | Location: columbus | Registered: August 01, 2018Reply With QuoteReport 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: 1918 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport 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: 871 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport 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: 1918 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.