In the following focexec, would it be possible to get an ON SUBHEAD CAR that would print all sales GT 10000 for that car?

 
TABLE FILE CAR
PRINT COUNTRY SEATS HEIGHT
BY CAR
END
 

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

Channy,

You want the total of all sales gt 10000? Or a list?

There has been a similar question recently. Look at this topic: (Sub)footing issue, it may give a hint on how you could accomplish something like what you want/need.

quote:

Originally posted by Danny-SRL:
Channy,

You want the total of all sales gt 10000? Or a list?

A List,

I will look at the link GamP provided

Channy,
Hope this helps.

TABLE FILE CAR
SUM 
COMPUTE SALE/D15.2 =  SALES; NOPRINT
BY CAR 
PRINT COUNTRY SEATS HEIGHT SALES
BY CAR 
WHERE TOTAL SALE GT 10000
ON CAR SUBHEAD    
"<SALE "   
END  

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

Suresh -

Can you describe what you were trying to show here?

Thanks so much.

trying to display the sum of sales for each car type that exceeds 10000 as subhead.
If you are expecting something else, please be more specific.

Yes, I was trying to get a list of those cars whose sales were greater than 10000 in the subhead for CAR.

Then i hope the above code matches the conditions you specified. Can you confirm upon this?

Do you need to show some attributes and/or summary measures for every account ("CAR") as a one-liner (regardless of the "sales" volume), and additionally list any transactions whose individual "sales" exceeds the threshold?

You may be able to get that (without resorting to subheads) by using multiple verbs, with an IF/WHERE TOTAL screen on an object of the second verb:

SUM ...
BY account (for the one-liner)

PRINT (or sum) amount ...
BY account
by date
WHERE TOTAL amount GE 10000;

test it to see what happens when an account has only small transactions. If it drops such accounts entirely, try with BY ALL.account.

quote:

Originally posted by SureshKumar:
trying to display the sum of sales for each car type that exceeds 10000 as subhead.
If you are expecting something else, please be more specific.

No, I was hoping to actually LIST the sales (as you have in the column that follows the subhead) - not a total of it

This does it.

MATCH FILE CAR
WRITE COUNTRY SEATS SALES AS CAR_Sales
 BY CAR
IF SALES NE 0
RUN
FILE CAR
SUM SALES AS MODEL_Sales
 BY CAR
 BY MODEL
IF SALES GT 10000
AFTER MATCH HOLD OLD
END

TABLE FILE HOLD
WRITE SEATS CAR_Sales/D10
 BY CAR
WRITE MODEL_Sales/D10S
 BY CAR
 BY MODEL AS 'Models with Sales > 10,000'
END

When I tried multiple verbs (even with ALL. or SET ALL=PASS) the report dropped CAR instances (e.g. AUDI) that had no qualified models. ALL will overcome short paths -- but here all the paths are full. If every row involving a particular parent is removed by screening on a field in the child segment, then the parent is effectively barred from the answer set. MATCH FILE overcomes this by forming two independent internal matrices, with different screening conditions, and then merges them -- allowing AUDI to enter the picture via the first leg of the Match.

In the "short" rows in the result, numeric fields would print as zero -- remedy that either by means of MISSING, or via a format with zero-suppression.

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

quote:

MATCH FILE CAR
WRITE COUNTRY SEATS SALES AS CAR_Sales
BY CAR
IF SALES NE 0
RUN
FILE CAR
SUM SALES AS MODEL_Sales
BY CAR
BY MODEL
IF SALES GT 10000
AFTER MATCH HOLD OLD
END

TABLE FILE HOLD
WRITE SEATS CAR_Sales/D10
BY CAR
WRITE MODEL_Sales/D10S
BY CAR
BY MODEL AS 'Models with Sales > 10,000'
END

Thanks - I'm still wondering if it's possible to get it as a subhead but I will play with it. Thanks so much for the example.

Here's a solution with subfoot:

  SET ASNAMES=ON, HOLDLIST=PRINTONLY
-* HOLD SALES DATA
  TABLE FILE CAR
    WRITE COUNTRY
    BY CAR BY HIGHEST SALES BY MODEL
  IF SALES NE 0
  ON TABLE HOLD AS HOLD1
  END
-RUN
-* HOLD MODELS WITH SALES OVER THRESHOLD
  TABLEF FILE HOLD1
    LIST SALES MODEL
    BY CAR
  IF SALES GE 10000
  ON TABLE HOLD AS HOLD2
  END
-RUN
-* GET REPEAT LIMIT
  TABLE FILE HOLD2
    WRITE MAX.LIST/I2
  IF LIST LE 99
  ON TABLE HOLD AS HOLD3 FORMAT ALPHA
  END
-RUN
-READ HOLD3 &N.A2
-? &N
-* FLATTEN (GIVES MOD1 SAL1 MOD2 SAL2 ETC)
  TABLE FILE HOLD2
    WRITE SALES MODEL
    ACROSS LIST
    BY CAR
  ON TABLE HOLD AS HOLD4
  END
-RUN
-* INDEX
  TABLE FILE HOLD4
    WRITE * BY CAR NOPRINT
  ON TABLE HOLD AS HOLD5 FORMAT FOCUS INDEX CAR
  END
-RUN
-* REPORT WITH CONDITIONAL SUBFOOTINGS
  JOIN CLEAR *
  JOIN CAR IN HOLD1 TO CAR IN HOLD5 AS J5
  TABLE FILE HOLD1
    SUM SALES BY CAR BY COUNTRY
-REPEAT FOOT FOR &I FROM 1 TO &N ;
    ON CAR SUBFOOT
" *** &I|. MODEL=<MOD&I|> SALES=<SAL&I|>"
    WHEN SAL&I NE 0
-FOOT
  END

It requires a series of Hold steps, but winds up with only one -REPEAT (to generate the subfoot code) and only one -READ (to get the repeat count).

I used LIST to generate the index values. That works as long as you are dealing with a simple (one field) key like CAR.

I tried combining the Flatten and Index steps, but the resulting Focus master retained the original fieldnames (SALES SALES ... rather than SAL1 SAL2 ...).

I was pleased to find that the field suffixes, based in an Integer-format across-variable, were assigned without leading zeros. That simplifies the -Repeat loop. I don't think that behavior is documented, so in production you might want to convert LIST to an A2 (01, 02, ...), and adjust the dialog manager code accordingly.

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

Wow - this is exactly what I was looking for. I need to work with it to get it working for the "real world". Thanks so much.

I try to keep use of Dialog Manager to a minimum -- D.M. does not comprehend the Focus command language or metadata, so I regard it as a much thinner limb than straight Focus.

The essential issue was how to make the MODEL data available for reporting at the CAR level, while avoiding the short-path issue (so AUDI will still print).

Along came the revival of the 2007 "Column Heading with Across" thread, and Kerry's contribution therein, which got me thinking that I could use ACROSS both to flatten the data (and make it a U-extension of the CAR segment) and to assign distinct fieldnames to reference in the series of SUBFOOT declarations ... and out popped this solution. So, tip-of-the-hat to Kerry.

The Hold Format Focus is not essential: In your real world, you may need multiple key values to identify the parent instance. Single or multiple keys, either way you should be able to avoid the "Index" step: Hold both the Parent-level (CAR) data, and the flattened Child-level (MODEL) data, sorted on the parent's keys, and you can use a fix-to-fix Join. -- Of course, "Parent-level key order" need not agree with the order of presentation in final report; once you provide for the Join of the incoming data, the final TABLE can sort things any way you want.