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] MacGyver Technique

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] MacGyver Technique
 Login/Join
 
Gold member
posted
Hi all,

May I get someone to explain the MacGyver technique to me? Or send me link to examples of it in use?

I have about 50k opportunities, a start date (01/2018) and end date (11/2018) and a value for the opportunities. I'm doing a date diff between the start and end date that equals '11' months between the 2 days. Now I divide the value by '11' and it equals 1k, so I have that. And then I want to display that value by each month 1/2018 - 1k, 2/2018 -1k etc.etc. Everyone I've talked to recommended the MacGyver technique, but I have no clue how to implement it.

I've looked over this article an I couldn't make sense of it. https://it.toolbox.com/questio...s-dynamically-083006

Thank you in advance.

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


WebFOCUS 8
Windows, All Outputs
 
Posts: 88 | Registered: December 06, 2016Report This Post
Expert
posted Hide Post
Have you checked out tips and techniques ?



Macgyver covers several techniques to make something out of nothing.

Check out FOCUS Techniques/Code Examples


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
Gold member
posted Hide Post
We have checked out the tips and techniques. Searched in focal point and found an example you alluded to but still can't understand how to implement.

http://forums.informationbuild...71057331/m/988102255

I looked over this example you pointed out but still no luck.

Do you have to understand the issue I'm trying to figure out from above?


WebFOCUS 8
Windows, All Outputs
 
Posts: 88 | Registered: December 06, 2016Report This Post
Expert
posted Hide Post
What are you trying to do ?

Examples would be useful, and end result.


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
Virtuoso
posted Hide Post
If I understand correctly, you want to enumerate each month between your start and end dates (regardless whether you have any results for that month)?

Using the McGyver technique boils down to creating a table with a row for each of those months, and a dummy field with the same value for all records. The dummy field you use to join your original table to.

The reason for the dummy field is that FOCUS cannot do a Carthesian product of 2 sets without a field to join on.

The table (let's call it 'B') looks like this:
month | dummy
------+------
    1 | ' '
    2 | ' '
    3 | ' '
  ... | ...
   11 | ' '


In your original table you also add that dummy field - and then you join table B to your opportunities result set.

JOIN LEFT_OUTER DUMMY IN B TO DUMMY IN OPPORTUNITIES AS J0


Most of the "complicated stuff" in those McGyver tutorials deals with creating table B, because you need a master file and you need to generate data.

It's possible that your database can generate this file for you, however. It's fairly common practice, for example, to have a calendar table with all dates in a wide range (extended when necessary), subdivided into quarters, working days, holidays, financial periods and the like. Other databases have functions from which you can generate a range of numbers - PostgreSQL has generate_series(start, end, step), for example.

In those cases you may not even need to do McGyver in WebFOCUS.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Gold member
posted Hide Post
Here's an example of what I'm trying to achieve.

Opportunity Close Date Start Date End Date Start to Close Booking Revenue Forecast
CAD Opportunity 12/31/2017 3/1/2018 3/1/2019 12 1,000,000 83,333.33
RMS Opportunity 12/31/2017 6/1/2018 12/31/2018 7 1,000,000 142,857.14




2018
Jan Feb March April May June July August September October November December
CAD Opportunity 83,333.33 83,333.33 83,333.33 83,333.33 83,333.33 83,333.33 83,333.33 83,333.33 83,333.33
RMS Opportunity 142,857.14 142,857.14 142,857.14 142,857.14 142,857.14 142,857.14


etc etc I couldn't add more months because of room.

Thanks for taking a look.


WebFOCUS 8
Windows, All Outputs
 
Posts: 88 | Registered: December 06, 2016Report This Post
Virtuoso
posted Hide Post
zcb,
You wondered about the MacGuyver technique. I am agreat believer and use it at any opportunity Wink

Here is a short lesson.

1. The MacGuyver Master File. I usually put it into the BASEAPP Application. Notice it uses a data file which is also in the BASEAPP Application. For some historical reason it has been named FSEQ... Confused
  
FILE=FSEQ, SUFFIX=FIX, DATASET=BASEAPP/FSEQ.TXT, $
  SEGNAME=SEG1
   FIELD=BLANK, BLANK , A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=WHATEVER, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$

2. The MacGuyver data file. Notice the first character must be a space.
 
 SOMECHARACTERSFORTHEFSEQFILENOTICETHEFIRSTCHARACTERISASPACE 

When you join a space in your data file to the space in the MacGuyver file this causes each record in your file to be duplicated the number of times you decide by limiting the ORDER field in the MacGuyver file.

Now for your example.
I created an OPPORTUNITY master:
 
FILENAME=OPPORTUNITY, SUFFIX=FIX     , $
  SEGMENT=OPPORTUNITY, SEGTYPE=S0, $
    FIELDNAME=OPPORTUNITY, USAGE=A16, ACTUAL=A16, $
    FIELDNAME=CLOSEDATE, USAGE=MDYY, ACTUAL=A8, $
    FIELDNAME=STARTDATE, USAGE=MDYY, ACTUAL=A8, $
    FIELDNAME=ENDDATE, USAGE=MDYY, ACTUAL=A8, $
    FIELDNAME=BOOKINGREVENUE, USAGE=I9, ACTUAL=A9, $

And an OPPORTUNITY data file
 
CAD Opportunity	123120170301201803012019  1000000
RMS Opportunity 123120170601201812312018  1000000

Both are in my FOCALPOINT Application

Lastly the fex to create your report:
-* FILEDEF points at the data  
FILEDEF OPPORTUNITY DISK FOCALPOINT/OPPORTUNITY.TXT
-RUN
-* JOIN the BLANK defined field to the BLANK in MacGuyver
JOIN BLANK WITH OPPORTUNITY IN OPPORTUNITY TO BLANK IN FSEQ AS M_

DEFINE FILE OPPORTUNITY
-* Define a one character BLANK field with the OPPORTUNITY field
BLANK/A1 WITH OPPORTUNITY=' ';

-* Calculate the number of months between start and end dates
MDIFF/I2=DATEDIF(STARTDATE, ENDDATE, 'D') / 30;
SMONTH/MYY=STARTDATE;
EMONTH/MYY=ENDDATE;

-* Create a running MONTH field by using the duplication of the MacGuyver file and the automatic numbering of the ORDER field
MONTH/MYY=SMONTH + ORDER - 1;
FORECAST/D11.2=BOOKINGREVENUE / MDIFF;
END

-* Generate all months
TABLE FILE OPPORTUNITY
PRINT OPPORTUNITY SMONTH EMONTH MDIFF MONTH FORECAST
WHERE COUNTER LE MDIFF;
ON TABLE HOLD
END
-RUN

-* Separate YEAR and MONTH for a matrix report
DEFINE FILE HOLD
YEAR/YY=MONTH;
MON/MTr=MONTH;
END

-* Output
SET NODATA=' '
TABLE FILE HOLD
SUM FORECAST
BY OPPORTUNITY
BY YEAR
ACROSS MON
END

Good luck!


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
Expert
posted Hide Post
quote:
Notice it uses a data file which is also in the BASEAPP Application


I think the original technique used the master file its self, therefore creating something out of one thing


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
Virtuoso
posted Hide Post
True Waz, I tried, but it doesn't work anymore...
I sort of researched it some time ago. I think it had to do with spaces or the $. Anyway, the data file did the trick.

This message has been edited. Last edited by: Danny-SRL,


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
Expert
posted Hide Post
This works for me.

The secret is to specify LRECL and RECFM in the DATASET specification.

Worked on V8.1.04 and 7.6.10

EX -LINES * EDAPUT MASTER,fseq,CV,FILE
FILE=FSEQ, SUFFIX=FIX, DATASET=fseq.mas (LRECL 1 RECFM F, $
  SEGNAME=SEG1
   FIELD=BLANK, BLANK , A1, A1, $
   DEFINE COUNTER/I9 = LAST COUNTER + 1 ;
EDAPUT*


-RUN

TABLE FILE FSEQ
PRINT BLANK COUNTER
-*WHERE RECORDLIMIT EQ 10
END


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
Virtuoso
posted Hide Post
Hhhhmmm, LRECL and RECFM in DATASET...
Will wonders ever cease
Good One

Still, you do need to have a child segment for the counter, don't you?

This message has been edited. Last edited by: Danny-SRL,


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
Virtuoso
posted Hide Post
This works for me:
  
 FILE=FSEQ, SUFFIX=FIX, DATASET=BASEAPP/fseq.mas (LRECL 180 RECFM F, $
  SEGNAME=SEG1
   FIELD=BLANK, BLANK , A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=WHATEVER, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$

With a space before FILE=


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
Platinum Member
posted Hide Post
As Danny says: "Will wonders ever cease?"
We are using the following on Windows - 8.2.01M
Perhaps mine works because I have OCCURS=150 in my example?
$$------------------------------------------------------------------------------------------------------------------------------------------
$$ Comments       : This Structure Is Used To Implement The MacGyver Technique And Should Not Be Altered
$$------------------------------------------------------------------------------------------------------------------------------------------
 $11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
FILENAME =MCGYVER,SUFFIX =FIX, DATASET=common/isa_u_mcgyver.mas,$
SEGNAME  =ROOT   ,SEGTYPE=S0
FIELDNAME=BLANK  ,           , FORMAT=A1,ACTUAL=A1,$
SEGNAME  =BABY   ,SEGTYPE=S0 , OCCURS=150
FIELDNAME=DUMMY  ,           , FORMAT=A1,ACTUAL=A1,$
FIELDNAME=COUNTER,ALIAS=ORDER, FORMAT=I4,ACTUAL=I4,$

And for the newbies - you could see how a self-generating data file works by saving the master file in an apps directory called "common" as "isa_u_mcgyver.mas" (in my example) - and then running the following request:
TABLE FILE ISA_U_MCGYVER
IF BLANK EQ ' '
PRINT COUNTER
END


WebFOCUS 8.2.06 mostly Windows Server
 
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008Report This Post
Virtuoso
posted Hide Post
Or you can create the replication file on the fly


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Master
posted Hide Post
As far as I'm concerned the definitive text on MacGyver is the Systems Journal Encyclopedia Vol 6 #2 from the Fall of 1994 and I've been using it about that long. It is required study for all new FOCUS/WebFOCUS programmers is my group. It has 14 separate articles by Noreen Redden and Art Greenhaus on using the MacGyver technique. The only thing I can find now are links to some of the articles that look like someone scanned the pages. Do a knowledge base search on Systems Journal Fall 1994. Look for articles named enc_fall_94_redden* and enc_fall_94_greenhaus*.

Below are the master and fex to load macgyver.foc. We store the master and focus file in baseapp.
FILE=MACGYVER,SUFFIX=FOC
SEGNAME=MAC1,SEGTYPE=S1
  FIELD=BLANK  ,     ,A1,INDEX=I,$
SEGNAME=MAC2,SEGTYPE=S1,PARENT=MAC1
  FIELD=COUNTER,ORDER,I4,$

-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


Basically what MacGyver does is let one process a single record COUNTER times. This means the record is read once and one can process that single record COUNTER ways in the internal matrix instead of reading the record COUNTER times. With this basic, all you need now it to use your imagination.

This message has been edited. Last edited by: jgelona,


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
Well, as usual, there are many blades to MacGuver...
Now we just have to wait and see how zcbillions manages! Music


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
Gold member
posted Hide Post
Wow Daniel, thank you soo much!

Very clean code, easy to follow along and it works perfectly.. man, you're a life safer! I appreciate it so much and you increased my skills in WebFocus.

The hardest part was creating the FSEQ file.. I created it in Data Migrator but my fex wasn't picking it up so I went ahead and created it in App Studio.

Thank you once again! And thank you for the discussion from everyone else. Much appreciated!


WebFOCUS 8
Windows, All Outputs
 
Posts: 88 | Registered: December 06, 2016Report This Post
Gold member
posted Hide Post
Sorry to come back to this, but I split up the months, and years into different reports.. How would I go about rolling up the months into quarters? Because it rolls up perfectly into the year bucket, but using the quarters it just renames the exact month buckets to quarter buckets?

Thank you.


WebFOCUS 8
Windows, All Outputs
 
Posts: 88 | Registered: December 06, 2016Report This Post
Expert
posted Hide Post
Usings Dannys example above, this is very simple. I'll leave the ACROSS interpretation for you to do Smiler

Rule 1: Don't over think the problem.

DEFINE FILE HOLD
YEAR/YY=MONTH;
MON/MTr=MONTH;
QTR/Q=MONTH;
END

-* Output
SET NODATA=' '
TABLE FILE HOLD
SUM FORECAST
BY OPPORTUNITY
BY YEAR
BY QTR
SUM FORECAST
BY OPPORTUNITY
BY YEAR
BY QTR
BY MON
END


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Virtuoso
posted Hide Post
I call the technique above the "double cheese" technique. Slang version: "Dubba' cheeze"... (for double BY field usage) LOL


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report 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] MacGyver Technique

Copyright © 1996-2020 Information Builders