Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Grouping a large number of records into one row

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Grouping a large number of records into one row
 Login/Join
 
Platinum Member
posted
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,


Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats
 
Posts: 121 | Location: California | Registered: June 19, 2008Report This Post
Virtuoso
posted Hide Post
try across

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




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Platinum Member
posted Hide Post
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


Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats
 
Posts: 121 | Location: California | Registered: June 19, 2008Report This Post
Virtuoso
posted Hide Post
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 ...


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
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




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Platinum Member
posted Hide Post
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!


Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats
 
Posts: 121 | Location: California | Registered: June 19, 2008Report This Post
Gold member
posted Hide Post
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?


WF: 8201, OS: Windows, Output: HTML, PDF, Excel
 
Posts: 78 | Registered: November 08, 2010Report This Post
Platinum Member
posted Hide Post
Crymsyn,

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


Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats
 
Posts: 121 | Location: California | Registered: June 19, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Grouping a large number of records into one row

Copyright © 1996-2020 Information Builders