This is an old problem that continues to plague me. In searching the forum I see that in the past I found a workaround, but that eludes me now.

Here's the situation: We sell products in several categories. Most are sold by weight, some are sold by the unit, and a few are sold by weight or unit. I need to sum VALUE WEIGHT and UNIT across CATEGORY. However, I don't want to include columns where all the rows are null (such as in most cases where we sell by WEIGHT only).

HIDENULLACRS and ACROSSPRT are the things that immediately spring to mind but I haven't got anywhere with them yet.

In this example the CATEGORIES are Solvent, Machinery, and Pigment. Solvents are sold purely by weight, Machinery purely as units, and Pigments can be either weight or units. (I have a lot more categories for this and the empty columns make for a very sparse spreadsheet output).

I want to suppress the Unit column for Solvents and the Weight column for Machinery

            Solvent                      Machinery                   Pigment
            Value    Weight   Unit       Value    Weight    Unit     Value    Weight    Unit
Product A   10,000    1,000      .           .         .       .         .         .       .
Product B        .        .      .       9,000         .       9         .         .       .
Product C        .        .      .           .         .       .     6,000       500      20

Here's my code: (FOBOT is VALUE, KGQTY is WEIGHT, UNQTY is UNITS, BL_YEAR is a YY field, PDESC is Product Description)

DEFINE FILE XYZ
CATG/A1=EDIT(ITEM_ID, '9$$$$$$$$$$$$$$$$$$$$$$$$');
LONG_CATG/A15=DECODE CATG(A 'Additive'
						C  'Chemical'
						D  'Drier'
						E  'Extender'
						F  'Finished'
						L  'Oil'
						P  'Pigment'
						Q  'Equipment/Part'
						R  'Resin'
						S  'Solvent'
						T  'Colorant'
						U  'Sundry'
						W  'Container'
						Z  'Equipment/Part'
						ELSE 'Unknown');
KGQTY/D12 MISSING ON=IF SOLDBY EQ 'LB' THEN PQTY * 0.4536 ELSE IF SOLDBY EQ 'UN' THEN MISSING ELSE PQTY;
UNQTY/D12S MISSING ON=IF SOLDBY EQ 'UN' THEN PQTY ELSE MISSING;
END

TABLE FILE XYZ
SUM
     FOBOT/D12
     KGQTY 
     UNQTY
BY  BL_YEAR
BY PDESC
ACROSS LONG_CATG
ON BL_YEAR SUBTOTAL
END

This message has been edited. Last edited by: George Patton,

Try to set it up so you can report

TABLE FILE XYZ
SUM
qty
BY BL_YEAR
BY PDESC
ACROSS LONG_CATG
ACROSS indx [NOPRINT]
ACROSS caption
ON BL_YEAR SUBTOTAL
END

with appropriate DEFINES, such that indx is 1 or 2 or 3 and caption is (correspondingly) Value, Weight or Unit.
If you play the cards right, Solvent will only have indx = 1 or 2, Machinery only 1 and 3, and Pigment all three indx values.

The point is that, with a single verb object, the inner ACROSS generates just the relevant columns for each outer ACROSS sort value, independently of the other outer ACROSS sort values.

Notes:
- ACROSS indx controls the order of presentation; without it the columns would be sorted across alphabetically (Unit, then Value, then Weight)

- depending upon the nature of your source data, you may need to first apply some data manipulation to get discrete row instances for the three caption values.

This message has been edited. Last edited by: j.gross,

Jack,

Thank you!

While standing in the shower it occurred to me that if I could get VALUE, WEIGHT, and UNITS categorized somehow then I could use a second ACROSS statement to work.

This is exactly what you have outlined. I'll have to study it some more to see if I can wrap my head around your suggestion in a concrete way, but don't be surprised if I come back to you for further education.

Thanks again!

Think Macgyver

Jack & George,
I would go for the alternate master technique:

  
-* File georgep03.fex
DEFINE FILE CAR
MSALES/I6 MISSING ON = IF SALES EQ 0 THEN MISSING ELSE SALES;
MRCOST/I6 MISSING ON = IF RCOST EQ 0 THEN MISSING ELSE RCOST;
MDCOST/I6 MISSING ON = IF DCOST EQ 0 THEN MISSING ELSE DCOST;
END
-*
TABLE FILE CAR
SUM MSALES MRCOST MDCOST
BY COUNTRY
BY MPG
ON TABLE HOLD AS GP FORMAT ALPHA MISSING ON
END
-RUN
EX -LINES 9 EDAPUT MASTER,GP,C,MEM
FILENAME=GP, SUFFIX=FIX
 SEGNAME=GP, SEGTYPE=S0
  FIELDNAME=COUNTRY, ALIAS=COUNTRY, USAGE=A10, ACTUAL=A10, $
  FIELDNAME=MPG, ALIAS=MPG, USAGE=D6, ACTUAL=A6, $
 SEGNAME=VALS, PARENT=GP, OCCURS=VARIABLE
  FIELDNAME=VAL, ALIAS=VAL, USAGE=I6, ACTUAL=A6, MISSING=ON, $
  FIELDNAME=VNUM, ALIAS=ORDER, USAGE=I1, ACTUAL=I4, $ 
DEFINE VNAME/A6=DECODE VNUM (1 MSALES 2 MRCOST 3 MDCOST ELSE N/A);, $
-RUN
TABLE FILE GP
SUM VAL
ACROSS MPG AS ''
ACROSS VNUM NOPRINT
ACROSS VNAME AS ''
BY COUNTRY
WHERE VAL NE MISSING;
END

Last night, starting with Jack's idea of getting the values on separate rows with an index, I thought I had come up with a solution using OVER to get an index and a value on the same row. Looks great on the display, but in a HOLD file the values all go back to being on a single line. grrrrr...

I hadn't thought about MacGuyver. It did occur to me in the shower that one way or another I would need an alternate master.

Danny's solution looks elegant and simple. I've actually used an alternate master with OCCURS=VARIABLE before, but couldn't wrap my head around how to make it work in this case. Danny's code should make it practically painless, so I'll give that a shot now.

This must be a common sort of problem and I'm surprised that there isn't a function or SET command to automatically take care of it. In the meantime I would encourage both Jack and Danny to work together to show both methods and post them in TIPS AND TECHNIQUES.

Thank you Danny and Jack !

(I'll mark this solved when my code is actually working)

This should do it.

  JOIN CLEAR *
  EX MAKESEQ FIRST=1,LAST=3
  JOIN BLANK WITH ITEM_ID IN XYZ TO BLANK IN FSEQ AS FSEQ:

DEFINE FILE XYZ
CATG/A1=EDIT(ITEM_ID, '9$$$$$$$$$$$$$$$$$$$$$$$$');
LONG_CATG/A15=DECODE CATG(A 'Additive'
                        C  'Chemical'
                        D  'Drier'
                        E  'Extender'
                        F  'Finished'
                        L  'Oil'
                        P  'Pigment'
                        Q  'Equipment/Part'
                        R  'Resin'
                        S  'Solvent'
                        T  'Colorant'
                        U  'Sundry'
                        W  'Container'
                        Z  'Equipment/Part'
                        ELSE 'Unknown');
KGQTY/D12 MISSING ON=IF SOLDBY EQ 'LB' THEN PQTY * 0.4536 ELSE IF SOLDBY EQ 'UN' THEN MISSING ELSE PQTY;
UNQTY/D12S MISSING ON=IF SOLDBY EQ 'UN' THEN PQTY ELSE MISSING;

  BLANK/A1 WITH ITEM_ID='';
  QTY / D12 MISSING ON = IF COUNTER EQ 1 THEN FOBOT ELSE IF COUNTER EQ 2 THEN KGQTY ELSE UNQTY;
  CAPTION/A6=DECODE COUNTER (1 'Value', 2 'Weight' 3 'Unit' ELSE '?');

END

TABLE FILE XYZ
SUM  QTY
BY  BL_YEAR
BY PDESC
ACROSS LONG_CATG

  ACROSS COUNTER NOPRINT
  ACROSS CAPTION AS ''
  WHERE COUNTER IN (1,2,3);
  WHERE QTY NE MISSING;

ON BL_YEAR SUBTOTAL
END  

The blocks of MacGyver-related code are indented. It's pretty compact, and doesn't require a HOLD step.

Not sure whether you really need the format differentiation (D12 vs D12S). If so, you can use dynamic formatting (as in a recent post), with
FMT/A8=DECODE COUNTER(1 'D12' 2 'D12' ELSE 'D12S');

MAKESEQ.fex is posted here.

This message has been edited. Last edited by: j.gross,

Here's my full solution using Danny's method. Thanks Danny!

I'll follow up with Jack's method and then mark it solved.

-SET &SALESREP = 'FP';
-SET &BEGYEAR = 2013;
 
DEFINE FILE NEWORDER
CATG/A1=EDIT(ITEM_ID, '9$$$$$$$$$$$$$$$$$$$$$$$$');
SOLDBY=IF SOLDBY EQ 'GL' OR 'LT' OR 'QT' OR 'UN' THEN 'UN' ELSE SOLDBY;
FOBVAL/D12=FOBT;
KGQTY/D12 MISSING ON=IF SOLDBY EQ 'LB' THEN PQTY * 0.4536 ELSE IF SOLDBY EQ 'KG' THEN PQTY ELSE MISSING;
UNQTY/D12 MISSING ON=IF SOLDBY EQ 'UN' THEN PQTY ELSE MISSING;
END

APP HOLD BASEAPP
-*
TABLE FILE NEWORDER
SUM FOBVAL KGQTY UNQTY
BY SALESREP
BY BL_YEAR
BY CCODE
BY CATG
BY PDESC
WHERE BL_YEAR GE &BEGYEAR 
AND SALESREP EQ '&SALESREP' 
AND DEPT EQ 'D' 
AND CCODE NE 'GUCA' OR 'GUQC' 
AND OSTAT EQ 'F' OR 'Y'
AND CATG NE 'U' OR 'X';
ON TABLE HOLD AS GP FORMAT ALPHA MISSING ON
END
-RUN

-*GOTO DONE
EX -LINES 12 EDAPUT MASTER,GP,C,MEM
FILENAME=GP, SUFFIX=FIX
 SEGNAME=GP, SEGTYPE=S0
  FIELDNAME=SALESREP, ALIAS=SALESREP, USAGE=A3, ACTUAL=A3, $
  FIELDNAME=BL_YEAR, ALIAS=BL_YEAR, USAGE=YY, ACTUAL=A4, $
  FIELDNAME=CCODE, ALIAS=CCODE, USAGE=A6, ACTUAL=A6, $
  FIELDNAME=CATG, ALIAS=CATG, USAGE=A1, ACTUAL=A1, $
  FIELDNAME=PDESC, ALIAS=PDESC, USAGE=A45, ACTUAL=A45, $
 SEGNAME=VALS, PARENT=GP, OCCURS=VARIABLE
  FIELDNAME=VAL, ALIAS=VAL, USAGE=D12, ACTUAL=A12, MISSING=ON, $
  FIELDNAME=VNUM, ALIAS=ORDER, USAGE=I1, ACTUAL=I4, $ 
DEFINE VNAME/A4=DECODE VNUM (1 FOB 2 KG 3 Unit ELSE N/A);, $
-RUN

DEFINE FILE GP
LONG_CATG/A15=DECODE CATG(A 'Additive'
			C  'Chemical'
			D  'Drier'
			E  'Extender'
			F  'Finished'
			L  'Oil'
			P  'Pigment'
			Q  'Equipment/Parts'
			R  'Resin'
			S  'Solvent'
			T  'Colorant'
			U  'Sundry'
			W  'Container'
			Z  'Equipment/Parts'
			ELSE 'Unknown');
END


TABLE FILE GP
SUM VAL
BY SALESREP NOPRINT
BY  BL_YEAR AS 'Year'
BY  CCODE AS 'Cust'
BY CATG NOPRINT
BY  PDESC
ACROSS LONG_CATG AS 'Category'
ACROSS VNUM NOPRINT
ACROSS VNAME AS '' 
ON BL_YEAR SUBTOTAL AS 'Total '
WHERE VAL NE MISSING
ON TABLE SUBHEAD
"                   Direct Sales for &SALESREP since &BEGYEAR"
ON TABLE SUBFOOT
" "
"Enter total target for 2016 on next line. Then calculate quarterly targets below"
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL07
ON TABLE SET STYLE *
     INCLUDE = PROBLUE,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=16,
$
TYPE=REPORT,
     COLUMN=N2,
     SQUEEZE=0.7,
$
ENDSTYLE
END

And here is the same solution using Professor Gross' McGuyver method. Thanks Jack !!

- I have appended Jack's MAKESEQ code at the bottom

JOIN CLEAR *

EX MAKESEQ FIRST=1,LAST=3

JOIN BLANK WITH ITEM_ID IN NEWORDER TO BLANK IN FSEQ AS FSEQ;

DEFINE FILE NEWORDER
CATG/A1=EDIT(ITEM_ID, '9$$$$$$$$$$$$$$$$$$$$$$$$');
LONG_CATG/A15=DECODE CATG(A 'Additive'
                        C  'Chemical'
                        D  'Drier'
                        E  'Extender'
                        F  'Finished'
                        L  'Oil'
                        P  'Pigment'
                        Q  'Equipment/Part'
                        R  'Resin'
                        S  'Solvent'
                        T  'Colorant'
                        U  'Sundry'
                        W  'Container'
                        Z  'Equipment/Part'
                        ELSE 'Unknown');
SOLDBY=IF SOLDBY EQ 'GL' OR 'LT' OR 'QT' OR 'UN' THEN 'UN' ELSE SOLDBY;
FOBVAL/D12=FOBT;
KGQTY/D12 MISSING ON=IF SOLDBY EQ 'LB' THEN PQTY * 0.4536 ELSE IF SOLDBY EQ 'UN' THEN MISSING ELSE PQTY;
UNQTY/D12 MISSING ON=IF SOLDBY EQ 'UN' THEN PQTY ELSE MISSING;
BLANK/A1 WITH ITEM_ID='';
QTY/D12 MISSING ON=IF COUNTER EQ 1 THEN FOBVAL ELSE IF COUNTER EQ 2 THEN KGQTY ELSE UNQTY;
CAPTION/A6=DECODE COUNTER (1 'FOB', 2 'KG' 3 'Unit' ELSE '?');
END

TABLE FILE NEWORDER
SUM QTY
BY SALESREP NOPRINT
BY BL_YEAR AS 'Year'
BY CCODE AS 'Cust'
BY CATG NOPRINT
BY PDESC
ACROSS LONG_CATG AS 'Category'
ACROSS COUNTER NOPRINT
ACROSS CAPTION AS ''
WHERE COUNTER IN (1,2,3);
WHERE QTY NE MISSING;
WHERE BL_YEAR GE &BEGYEAR 
AND SALESREP EQ '&SALESREP' 
AND DEPT EQ 'D' 
AND CCODE NE 'GUCA' OR 'GUQC' 
AND OSTAT EQ 'F' OR 'Y'
AND CATG NE 'U' OR 'X';
ON BL_YEAR SUBTOTAL AS 'Total '
ON TABLE SUBHEAD
"                   Direct Sales for &SALESREP since &BEGYEAR"
ON TABLE SUBFOOT
" "
"Enter total target for 2016 on next line. Then calculate quarterly targets below"
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL07
ON TABLE SET STYLE *
     INCLUDE = PROBLUE,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=16,
$
TYPE=REPORT,
     COLUMN=N2,
     SQUEEZE=0.7,
$
ENDSTYLE
END  

Here is MAKESEQ:

-* File makeseq.fex
-* McGuyver technique
-* Courtesy of Jack Gross

-DEFAULTH &FILE=FSEQ, &KEY=BLANK, &COUNTER=COUNTER, &FIRST=1, &LAST=100
 EX -LINES 7
 EDAPUT MASTER, &FILE, CV, MEM
  FILE=&FILE, SUFFIX=FOC, $
    SEGNAME=FSEQ1, SEGTYPE=S1, $
      FIELD=&KEY, , A1, ACCEPT=' ', INDEX=I, $
    SEGNAME=FSEQ2, SEGTYPE=S1, PARENT=FSEQ1, $
      FIELD=&COUNTER, , I4, $
 END
 CREATE FILE &FILE
 MODIFY FILE &FILE
  FREEFORM &KEY &COUNTER
  MATCH &KEY &COUNTER
    ON NOMATCH INCLUDE
    ON MATCH REJECT
 DATA
-REPEAT SEQ.LOOP FOR &I FROM &FIRST TO &LAST ;
 ' ', &I, $
-SEQ.LOOP
 END

Jack,
I always used a sequential MacGuyver file. Is your FOCUS file more efficient?

George,
Spending all that time in the water, are you thinking of changing your name to Archimedes?
Anyway, nice going and thanks for both examples.

Might be: As a compact Focus file its (typically) three pages will be read into memory (remember SET BINS?).

Not sure what happens in processing the join with a flat file.

As compared with using a Hold step and alt master: That must be a bit slower. But normally not enough to matter.

Well, after doing all of that work, I hate to tell you that the "Sales Director" couldn't understand the spreadsheet that resulted: "It is too difficult for me ...."

So now I am back to square one developing a solution that will require the sales reps to enter targets for next year for every product for every customer rather than doing it by product category for the territory as a whole. For some of them that will mean calculating and then entering 2000 different values, rather than just 24 - either on a spreadsheet (which I don't like) or, if I can figure it out, directly into a database that I'll have to set up. Either way, I'm sure they will be pleased as punch ....

This message has been edited. Last edited by: George Patton,

PS. Given Jack's generic McGuyver routine that can be plugged in almost anywhere, I believe his solution is the most efficient both in terms of processing and time taken for programming.

If I understand his code, there are just 3 iterations of the MODIFY in this case. There is also no need to write out any alternate master. (I did spend quite a lot of time with that because I mis-remembered what the original database looked like and had the field length wrong for the product description, which subsequently threw off all the subsequent columns).

With the McGuyver method I could work with the original field names that are ingrained in my brain after 30 years at this, and remain relatively ignorant of lengths etc. So ultimately faster to program (specially since Jack laid it all out for me!)

...And it's parametrized, so if you need two such joins just name them differently using the &FILE parameter (ditto for their fieldnames).

EX -LINES is nice that way -- it's a feature built into EX, which is a Focus command (rather than a Dialog Manager directive) so it doesn't actually do anything until it (the EX command line) and the lines that follow are read from FocStack, and at that point all the amper references have long since been converted to plain text strings.