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,

Have you checked out tips and techniques ?



Macgyver covers several techniques to make something out of nothing.

Check out FOCUS Techniques/Code Examples

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?

What are you trying to do ?

Examples would be useful, and end result.

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.

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.

zcb,
You wondered about the MacGuyver technique. I am agreat believer and use it at any opportunity

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...

  
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!

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

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,

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

Hhhhmmm, LRECL and RECFM in DATASET...
Will wonders ever cease


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,

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=

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

Or you can create the replication file on the fly

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,

Well, as usual, there are many blades to MacGuver...
Now we just have to wait and see how zcbillions manages!

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!

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.

Usings Dannys example above, this is very simple. I'll leave the ACROSS interpretation for you to do

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

I call the technique above the "double cheese" technique. Slang version: "Dubba' cheeze"... (for double BY field usage) LOL