I have some data organized as shown below. I want to create a custom Table of Contents that will concatenate the first and last VALUES in a BLOCK. In the example shown below the first two entries in the TOC would be...
'2 Wheel Tractors - Chainsaws'
'Chippers - Dumpers'

Maybe because it's Friday late in the day but I'm having a hard time coming up with the concept for grabbing the values from ROW_NUM 1, 50, 51, 100 and so on.

Will somebody please kickstart me in the right direction?

BLOCK_NUM      ROW_NUM      VALUES
---------      -------      ------
1              1            2 Wheel Tractors
|
|
|
|
|
|
1              50           Chainsaws
2              51           Chippers
|
|
|
|
|
|
2              100          Dumpers

Thanks!

Dan

This message has been edited. Last edited by: Dan Pinault,

For starters: use the LAST function (IF VALES EQ / NE LAST VALUES) in your DEFINE FILE block to determine whether or not the value of VALUES is within the same BLOCK_NUM. DEFINE TOC_ITEM/A99 (or whatever is required). Concatenate accordingly (concatenate LAST VALUES to TOC_VALUE, when the VALUES NE LAST VALUES). Hint: The first value of VALUES is NE to LAST value of VALUES. These are the basic building blocks that you can use - for a kickstart. I'll leave the design up to you. If you have difficulty with this then I'd recommend that you read up on those keywords and the usage thereof.

Have Fun with it.

Thanks Doug. That is just what I needed. Here is what I came up with (using PERSINFO for example)

SET ASNAMES = ON
-DEFAULT &FIELDNAME = 'PIN';
TABLE FILE PERSINFO
SUM FST.&FIELDNAME
RANKED AS ROWNUM BY &FIELDNAME
ON TABLE HOLD AS FOCCACHE/FIELDHOLD
END
TABLE FILE FOCCACHE/FIELDHOLD
PRINT
&FIELDNAME
ROWNUM
RANKED AS BLOCKNUM BY ROWNUM NOPRINT IN-GROUPS-OF 10
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD AS FOCCACHE/BLOCKHOLD
END
DEFINE FILE FOCCACHE/BLOCKHOLD
CONCAT1/A99V = IF (BLOCKNUM NE LAST BLOCKNUM) THEN (&FIELDNAME);
CONCAT1A/A99V = LAST CONCAT1;
CONCAT2/A99V = IF (BLOCKNUM NE LAST BLOCKNUM) THEN (LAST &FIELDNAME) ELSE ('');
END
TABLE FILE FOCCACHE/BLOCKHOLD
PRINT BLOCKNUM ROWNUM &FIELDNAME CONCAT1 CONCAT1A CONCAT2 MAX.ROWNUM WITHIN TABLE
COMPUTE CONCAT2A/A99V = IF (ROWNUM EQ MAX.ROWNUM) THEN (&FIELDNAME) ELSE (CONCAT2);
COMPUTE TOC_ITEM/A99V = IF (CONCAT2A NE '')OR(ROWNUM EQ MAX.ROWNUM) THEN (CONCAT1A || ' - ' | CONCAT2A) ELSE ('');
COMPUTE TOC_BLOCK/I8 = IF(CONCAT2A NE '') THEN (IF (ROWNUM EQ MAX.ROWNUM) THEN (BLOCKNUM) ELSE (BLOCKNUM - 1));
ON TABLE HOLD AS FOCCACHE/TOCHOLD
END
TABLE FILE FOCCACHE/TOCHOLD
SUM FST.TOC_ITEM
BY TOC_BLOCK
WHERE TOC_BLOCK NE 0;
ON TABLE PCHOLD FORMAT HTML
END

Cheers!

Dan

This message has been edited. Last edited by: Dan Pinault,

IN-GROUPS-OF 10 will partition the integer-valued field ROWNUM into [0-9], [10-19], etc. -- the lower bounds of the groups are multiples of the grouping factor. Since you are grouping on ROWNUM, which is defined to start from 1, your ranges will contain 1-9, 10-19, ...
Consequently,
(a) The first block will be one shorter than the rest, and
(b) if the population is an exact multiple of 10 your TOC winds up with an extra block covering a single value.

In short, it's better (marginally) to sort on an index that starts from zero (=ROWNUM-1), sorting that "in groups of" n, to partition the population and assign block numbers.

Jack,

You are correct. I did see that the first block had one less member than the rest and that the block numbering started from zero. Eventually I correct that with

COMPUTE TOC_BLOCK/I8 = IF(CONCAT2A NE '') THEN (IF (ROWNUM EQ MAX.ROWNUM) THEN (BLOCKNUM) ELSE (BLOCKNUM - 1));

Are you suggesting a different method than using RANK? Or perhaps adjusting the RANK value (RANK - 1) sooner?

Thanks,

Dan

How 'bout this.

-* File BLOCKS.fex

-DEFAULT &FILE=CAR, &KEY=CAR, &BLOCKSIZE=3
-DEFAULT &BLOCK=0
-IF &BLOCK GT 0 THEN GOTO BLOCKS.one ;

-* hold keys with assigned rownum and computed block number
TABLE FILE &FILE
  WRITE
    COMPUTE ROWNUM/I7 = LAST ROWNUM+1;
    COMPUTE BLOCK /I5 = 1+INT((ROWNUM-1)/&BLOCKSIZE);
  BY &KEY
ON TABLE HOLD AS FOCCACHE/HOLDKEYS FORMAT FOCUS INDEX BLOCK
END
-RUN
-* return list of blocks
TABLEF FILE FOCCACHE/HOLDKEYS
 WRITE
 FST.ROWNUM AS LOROW  NOPRINT
 LST.ROWNUM AS HIROW  NOPRINT
 FST.&KEY   AS LOKEY  NOPRINT
 LST.&KEY   AS HIKEY  NOPRINT
 COMPUTE RANGE/A50 =
   IF LST.ROWNUM GT FST.ROWNUM
     THEN FST.&KEY || (' ~ ' | LST.&KEY)
     ELSE FST.&KEY;
 BY BLOCK
ON TABLE SET HOLDLIST PRINTONLY AND ASNAMES ON
ON TABLE PCHOLD FORMAT XML
END
-GOTO BLOCKS.end

-BLOCKS.one
-* return list of keys within requested block
TABLE FILE FOCCACHE/HOLDKEYS.BLOCK
 PRINT &KEY
 BY &KEY
 IF BLOCK EQ &BLOCK
ON TABLE PCHOLD FORMAT XML
END
-BLOCKS.end

As indicated in the comments, the routine is double-purpose --

• (TOC request) EX with BLOCK=0 (default) and it creates the data file in Foccache and returns the TOC.
  
• (Detail request) With BLOCK>0 it returns the detail lines for that block.
  

I like it! Definitely more efficient use of code than my version.

Now I remember why I used RANK instead of a COMPUTE. There is a bug in 7.6.9 where a plus sign (+) is replaced by a blank when the code is rendered from the HTML file. It works as a plain fex but now when it is part of a control on an HTML page. To get around that I had to come up with a way to do this without using a plus sign. Thus the RANK method.

When the bug is fixed I'll implement your version.

Thanks again!

Dan

In that case, a workaround is to change +x to -(-x).

But if you store the code as a fex on the server, and limit the imbedded procedure code to an EX statement with appropriate parameters, the code becomes reusable (within the page and across pages), and nary a + in the HTML.

And that's why you get the karma level of 'master'!

Jack,

I've been looking at the code you posted here. Man, this is great! I guess I code like I fix my car - I usually take more stuff off than I need, only to create more work for myslef when it comes time to re-assemble.

I didn't realize you could use LAST on a virtual field like you do in the ROWNUM compute. Very helpful.
The simple math to calculate the BLOCK - it's so obvious when I look at it!

Question - why do you use the WRITE verb? Is there any advantage over using SUM?

Anyway, I appreciate your help. The techniques you show here will help me many more times!

Regards,

Dan

Dan,
WRITE is synonymous with SUM, as is ADD. It's just a thing of what you're used to.
One is not better than the other, they're just the same.