Focal Point
Concatenating row data
June 04, 2008, 11:33 AM
Jay HarmorConcatenating row data
I am trying to multiple rows of a text column for output into a file that a user will use to load a memo field in a table. The data is simple, record ID (A8) and comments (A1 -A255). The end result I am looking for is one comments field (A3000) per record id so that all the individual comment rows are concatenated together.
Current:
RecID Comments
11111111 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
11111111 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
11111111 cccccccccccccccccccccccccccccccccc
22222222 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
22222222 ******************************
Desired:
RecID Comments
11111111 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa bbbbbbbbb bbbbbbbbbbbbbbbbbbbb ccccccccccccccccccc
22222222 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhh *********k
***************************************kk
I have tried one the techniques from similar posts:
NEWFIELD/A3000 = IF RECID EQ LAST RECID THEN SUBSTR(255, NEWFIELD 55,’A255’) || ( ‘’ | COMMENTS) ELSE COMMENTS
But I only seem to return the first and last comment rows in the new field.
Thanks
WebFOCUS 7.1.3
Windows XP
June 04, 2008, 12:10 PM
Francis MarianiYour're not concatenating COMMENTS to previous COMMENTS, you're wiping them out with the code as shown. Note that your data has to be in the correct order for the LAST to work.
Here's a working example using the CAR file:
SET HOLDFORMAT = ALPHA
SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
-RUN
TABLE FILE CAR
PRINT
COUNTRY MODEL
BY COUNTRY NOPRINT
BY MODEL NOPRINT
ON TABLE HOLD AS H001
END
-RUN
TABLE FILE H001
SUM
COMPUTE NEWFIELDT/A3000V =
IF COUNTRY EQ LAST COUNTRY THEN NEWFIELD || (' ' | MODEL) ELSE COUNTRY || (' ' | MODEL); NOPRINT
COMPUTE NEWFIELD/A3000 = NEWFIELDT;
BY COUNTRY NOPRINT
ON TABLE HOLD AS H002
END
TABLE FILE H002
PRINT *
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 05, 2008, 03:41 PM
Jay HarmorFrancis,
I am working with your solution and I seem to only get the last comment. For Example
RecID Comments
11111111 cccccccccccccccccccccccc
22222222 ************************
I have verified that the records and comments are in the proper order. It looks like the compute overwrites the value newfieldt each time and dosen't apend through each step of the loop. I really am tring to create a variable and append the value of comments to it until the value of RECID changes. So far no luck.
WebFOCUS 7.1.3
Windows XP
June 06, 2008, 08:51 AM
PBrightwellYour substring value on NEWSTRING should be 2745. (the size of NEWSTRING 3000, minus the size of COMMENTS 255).
Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
June 06, 2008, 09:40 AM
j.grossThe COMPUTE in Francis's second step will list only one MODEL value per COUNTRY (the summed value of the implicit verb object MODEL).
Instead, assemble the list in a DEFINE on the first hold, then hold the last value per country.
TABLE FILE CAR
COUNT ENTRIES BY COUNTRY BY MODEL
ON TABLE HOLD AS H001
END
DEFINE FILE H001
RESULT/A3000V = IF (COUNTRY EQ LAST COUNTRY)
THEN RESULT || (' ' | MODEL)
ELSE COUNTRY | (' ' | MODEL);
END
TABLE FILE H001
SUM LST.RESULT/A3000 BY COUNTRY
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS H002
END
TABLE FILE H001
LIST *
END
TABLE FILE H002
LIST *
END
- Jack Gross
WF through 8.1.05
June 09, 2008, 11:52 AM
Jay HarmorI was able to get my results with Jack's example.
Thanks.
WebFOCUS 7.1.3
Windows XP
June 09, 2008, 12:04 PM
Francis MarianiJack, thanks for fixing that code!
Here's mine, slightly tweaked:
SET HOLDFORMAT = ALPHA
SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
-RUN
TABLE FILE CAR
PRINT
COUNTRY MODEL
BY COUNTRY NOPRINT
BY MODEL NOPRINT
ON TABLE HOLD AS H001
END
-RUN
DEFINE FILE H001
NEWFIELDT/A3000V =
IF COUNTRY EQ LAST COUNTRY THEN NEWFIELD || (' - ' | MODEL) ELSE COUNTRY || (': ' | MODEL); NOPRINT
COMPUTE NEWFIELD/A3000 = NEWFIELDT;
END
TABLE FILE H001
SUM
NEWFIELDT/A3000
BY COUNTRY NOPRINT
-*BY MODEL NOPRINT
ON TABLE HOLD AS H002
END
TABLE FILE H002
PRINT *
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 11, 2008, 09:55 AM
Jay HarmorThanks for the other approach. I'm starting to figure out how WF works.
WebFOCUS 7.1.3
Windows XP