Can someone please help me figure out or point me in the right direction on trying to group fields together to make the data output on one row. I tried many calculations but I could not group the data together correctly. Please let me know if you need additional information.

OLD:

YEAR_____MAKER____WOOD_____STYLE____TOAST_____CAP______BARREL_ID
2006_____BOU______FCS______NO_______M+________70_______153925
2006_____BOU______FCS______NO_______M+________70_______153926
2006_____BOU______FCS______NO_______M+________70_______153927
2006_____BOU______FCS______NO_______M+________70_______153928
2006_____BOU______FCS______NO_______M+________70_______153929
2006_____BOU______FCS______NO_______M+________70_______153930
2006_____BOU______FCS______NO_______M+________70_______153931
2006_____BOU______FCS______NO_______M+________70_______153932
2006_____BOU______FCS______NO_______M+________70_______153933
2006_____BOU______FCS______NO_______M+________70_______153936
2006_____BOU______FCS______NO_______M+________70_______153937
2006_____BOU______FCS______NO_______M+________70_______153938
2006_____BOU______FCS______NO_______M+________70_______153939
2006_____BOU______FCS______NO_______M+________70_______153940
2006_____BOU______FCS______NO_______M+________70_______153941
2006_____BOU______FCS______NO_______M+________70_______153942
2006_____BOU______FCS______NO_______M+________70_______153943
2006_____ BOU______FCS______NO_______M+________70_______153944
2006_____BOU______FCS______NO_______M+________70_______153953
2006_____BOU______FCS______NO_______M+________70_______153954
2006_____BOU______FCS______NO_______M+________70_______153955
2006_____BOU______FCS______NO_______M+________70_______153956
2006_____BOU______FCS______NO_______M+________70_______153958

What I would like it to look like below:

YEAR_____MAKER____WOOD_____STYLE_____TOAST____CAP______BARREL_ID
2006_____BOU______FCS______NO________M+_______70_______153925-153933, 153936-153944, 153953-153956, 153958

Thanks!

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

try across

table file xxx
list
fields
on table hold
end
table file hold
sum barrel_id
by...xxxx
across list
end

One thing i forgot to mention if the column Barrel_ID is continous then take the minimum barrel_id and the maximum barrel_id of the continous barrel_id (ex. 153925-153933). If the next barrel_id is not continous then add a comma and start a new grouping. (ex. 153925-153933, 153936-153944) Look at the example below that i would like the report to look like and you will see what i mean.

YEAR_____MAKER____WOOD_____STYLE_____TOAST____CAP______BARREL_ID
2006_____BOU______FCS______NO________M+_______70_______153925-153933, 153936-153944, 153953-153956, 153958

Something like this might do the trick for you.

-* File fptest.fex
-SET &ECHO='ALL';

EX -LINES 10 EDAPUT MASTER,barrel,CV,FILE
FILENAME=BARREL, SUFFIX=FIX
SEGNAME=BARREL
FIELDNAME=YEAR     , FORMAT=A4, ACTUAL=A4, $
FIELDNAME=MAKER    , FORMAT=A3, ACTUAL=A3, $
FIELDNAME=WOOD     , FORMAT=A3, ACTUAL=A3, $
FIELDNAME=STYLE    , FORMAT=A2, ACTUAL=A2, $
FIELDNAME=TOAST    , FORMAT=A2, ACTUAL=A2, $
FIELDNAME=CAP      , FORMAT=A2, ACTUAL=A2, $
FIELDNAME=BARREL_ID, FORMAT=I6, ACTUAL=A6, $

EX -LINES 47 EDAPUT FOCTEMP,barrel,CV,FILE
2006BOUFCSNOM+70153925
2006BOUFCSNOM+70153926
2006BOUFCSNOM+70153927
2006BOUFCSNOM+70153928
2006BOUFCSNOM+70153929
2006BOUFCSNOM+70153930
2006BOUFCSNOM+70153931
2006BOUFCSNOM+70153932
2006BOUFCSNOM+70153933
2006BOUFCSNOM+70153936
2006BOUFCSNOM+70153937
2006BOUFCSNOM+70153938
2006BOUFCSNOM+70153939
2006BOUFCSNOM+70153940
2006BOUFCSNOM+70153941
2006BOUFCSNOM+70153942
2006BOUFCSNOM+70153943
2006BOUFCSNOM+70153944
2006BOUFCSNOM+70153953
2006BOUFCSNOM+70153954
2006BOUFCSNOM+70153955
2006BOUFCSNOM+70153956
2006BOUFCSNOM+70153958
2006BOUFCSNOX+70156925
2006BOUFCSNOX+70156926
2006BOUFCSNOX+70156927
2006BOUFCSNOX+70156928
2006BOUFCSNOX+70156929
2006BOUFCSNOX+70156930
2006BOUFCSNOX+70156931
2006BOUFCSNOX+70156932
2006BOUFCSNOX+70156933
2006BOUFCSNOX+70156936
2006BOUFCSNOX+70156937
2006BOUFCSNOX+70156938
2006BOUFCSNOX+70156939
2006BOUFCSNOX+70156940
2006BOUFCSNOX+70156941
2006BOUFCSNOX+70156942
2006BOUFCSNOX+70156943
2006BOUFCSNOX+70156944
2006BOUFCSNOX+70156953
2006BOUFCSNOX+70156954
2006BOUFCSNOX+70156955
2006BOUFCSNOX+70156956
2006BOUFCSNOX+70156958

FILEDEF BARREL DISK barrel.ftm (LRECL 22 RECFM V
-RUN

DEFINE FILE BARREL
CONT/A1 = IF YEAR      NE LAST YEAR          THEN 'R' ELSE
          IF MAKER     NE LAST MAKER         THEN 'R' ELSE
          IF WOOD      NE LAST WOOD          THEN 'R' ELSE
          IF STYLE     NE LAST STYLE         THEN 'R' ELSE
          IF TOAST     NE LAST TOAST         THEN 'R' ELSE
          IF CAP       NE LAST CAP           THEN 'R' ELSE
          IF BARREL_ID EQ LAST BARREL_ID + 1 THEN 'Y' ELSE 'N';
SHOW/A300V = IF CONT EQ 'R' THEN EDIT(BARREL_ID) ELSE
             IF CONT EQ 'Y' THEN SHOW ELSE SHOW | '-' | EDIT(LAST BARREL_ID) | ', ' | EDIT(BARREL_ID);
END
TABLE FILE BARREL
SUM   SHOW AS 'BARREL_IDs'
BY    YEAR
BY    MAKER
BY    WOOD
BY    STYLE
BY    TOAST
BY    CAP
ON    TABLE SET BYDISPLAY ON
END

Hope this helps ...

first you have to control if the barrelID is continuous

TABLE FILE XXX
PRINT fields
COMPUTE SEQGR/I2=IF LAST BARRELID+1 EQ BARRELID THEN SEQGR=LAST SEQGR ELSE LAST SEQGR+1;
BY BARRELID
ON TABLE HOLD
END

Now you have vreated a temporary table with some extra numbers. a sequence number and a groupnumber

the next step is to find the lowest and highest number within a group.

TABLE FILE HOLD
SUM MIN.BARREL MAX.BARREL
BY SEQGR
BY other fields
END

try this and see if this gives you more or less what you want.

the next step is to concatenate the minimum and maximum field to a string, and if the min and max are the same make it to a single field.
You have to do that in a define.
If that works you can do what I suggested in my first answer.

good luck

GamP,

The code you provided worked prefect! The only question I had was if the barrel_id is continous then it stops then continous again but all of a sudden there is one barrel_id by itself then its continous again. How do i get that one barrel_id by itself? The other thing is the leading zero's on the barrel_id's but i can fix that.

Below is an example of what the code is doing with the code you provided:

0000157032-0000157034, 0000157036-0000157131, 0000157133-0000157133, 0000157135-0000157138, 0000157140-0000157143, 0000157505

The first 2 groupings worked fined but as you can see the 3 one is just one barrel_id but the code you provided shows that one barrel_id and dashes itself (0000157133-0000157133).

I would like for the outcome to look like below:

0000157032-0000157034, 0000157036-0000157131, 0000157133, 0000157135-0000157138, 0000157140-0000157143, 0000157505

Thanks!

Does modifying GamP's Define from

SHOW/A300V = IF CONT EQ 'R' THEN EDIT(BARREL_ID) ELSE
             IF CONT EQ 'Y' THEN SHOW ELSE SHOW | '-' | EDIT(LAST BARREL_ID) | ', ' | EDIT(BARREL_ID);

to

SHOW/A300V = IF CONT EQ 'R' THEN EDIT(BARREL_ID) ELSE
             IF CONT EQ 'Y' THEN SHOW ELSE 
             IF LAST CONT EQ 'Y' THEN SHOW | '-' | EDIT(LAST BARREL_ID) | ', ' | EDIT(BARREL_ID) ELSE SHOW | ', ' | EDIT(BARREL_ID);

work?

Crymsyn,

It worked!! Thank you so much. Thank you all for all your help.