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     concatenating rows of data

Read-Only Read-Only Topic
Go
Search
Notify
Tools
concatenating rows of data
 Login/Join
 
<Kalyan>
posted
Hi,
I need to concatenate multiple rows of data in to one single row.
eg:
emp no emp comments
123 i do not
123 know how to
123 do it

Assuming I have the above data in a table how do i convert it ito something like this:
emp no emp comments
123 i do not know how to do it

This final table should be kept in a hold file. Any suggestions???

Thanks.
 
Report This Post
<kj>
posted
Kalyan,
First sort the records by emp no.
Use the LAST command to compare the last previous record emp no and curent record emp no. If the emp no is same, then append the comments to a defined field.

hope it helps.

kj
 
Report This Post
<Thiru>
posted
Kalyan,

I had a similar situation where I got good replies in this forum back in last July. Search for my member number 515 and look for the topic "Create an addtional record on a HOLD file".

Hope this will help you!

--Thiru
 
Report This Post
Expert
posted Hide Post
then there's always
LIST COMMENT BY EMPNO
ON TABLE HOLD
END
TABLE FILE HOLD
SUM COMMENT ACROSS E01 BY EMPNO
END
...
E01 is the alias to field 1 in an interim hold file...and the LIST verb creates an integer ranking. if you have 3 comments per employee, you'll have a max value of 3 for the E01 field
for each employee number.
Depends on how nice you want your output to look.
This is just a quick and dirty way to slap it into one record.
Then you could define a field that concatenates
all the comments together, and removes multiple space, etc (see the CHARACTER functions in manual #4 Using FUnctions)
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
<Kalyan>
posted
I used the LAST COMMAND by "kj".. Suited my purpose fine...
Thanks anyway for all your ideas...
 
Report This Post
<Kalyan>
posted
There's still a problem in concatenating multiple rows for a single value.

KJ...your idea works, but i was able to concatenate just two rows of data using the LAST command. When there are more than two rows, only two adjacent rows are concatenated.

Susannah... SUM doesnt seem to work for character strings. Just one record is fetched when i use sum.

More light please....
 
Report This Post
Guru
posted Hide Post
SUM with Alphanumeric will grab the last record.

you can do a
SUM yourfield
BY yourfield NOPRINT

to accomplish what you need!
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
Platinum Member
posted Hide Post
This is primitive but it works.
-* test data ********************************************
-*123i do not
-*123know how to
-*123do it
-*456this is how
-*456you do it
-*789it is
-*789a lot
-*789harder than
-*789it looks
-* mfd ***************************************************
*FILE=TESTDES1 ,SUFFIX=FIX
*SEGNAME=TESTDES1 ,SEGTYPE=S01
-*FIELDNAME =EMP_NO ,E01 ,A3 ,A3 ,$
-*FIELDNAME =EMP_COMMENTS ,E02 ,A11 ,A11 ,$
-**********************************************************
FILEDEF TESTDES1 DISK C:\IBI\APPS\SAMPLES\TESTDES1.FTM
-**********************************************************
TABLE FILE TESTDES1
SUM CNT.EMP_NO
BY EMP_NO
LIST EMP_COMMENTS
BY EMP_NO
ON TABLE HOLD
END
DEFINE FILE HOLD
COMMENTS1/A11 = IF LIST EQ 1 THEN E04;
COMMENTS2/A23 = IF LIST EQ 2 THEN COMMENTS1| ' ' | E04 ;
COMMENTS3/A35 = IF LIST EQ 3 THEN COMMENTS2| ' ' | E04 ;
COMMENTS4/A47 = IF LIST EQ 4 THEN COMMENTS3| ' ' | E04 ELSE ' ';
COMMENTS5/A59 = IF LIST EQ 5 THEN COMMENTS4| ' ' | E04 ELSE ' ';
NEW_COMMENTS/A100 = IF E02 EQ 1 THEN COMMENTS1 ELSE
IF E02 EQ 2 THEN COMMENTS2 ELSE
IF E02 EQ 3 THEN COMMENTS3 ELSE
IF E02 EQ 4 THEN COMMENTS4 ELSE
IF E02 EQ 5 THEN COMMENTS5 ELSE 'need more comment fields';
END
TABLE FILE HOLD
SUM NEW_COMMENTS
BY E01
END
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Report This Post
Guru
posted Hide Post
Try this...

TABLE FILE TESTDES1
PRINT

COMPUTE
COMM1/A100 = IF EMP_NO EQ LAST EMP_NO THEN
(SUBSTR(100, (LAST COMM1), 1, 88, 88, 'A88')
|| (' ' | EMP_COMMENTS)) ELSE EMP_COMMENTS;

COMPUTE
CTR/I3 = IF EMP_NO EQ LAST EMP_NO
THEN CTR + 1 ELSE 1;
NOPRINT

BY EMP_NO
BY TOTAL HIGHEST 1 CTR NOPRINT
END
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
Platinum Member
posted Hide Post
K. Piippo, very good I like that. That works perfect.
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Report 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     concatenating rows of data

Copyright © 1996-2020 Information Builders