Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     concatenating rows of data
Go
New
Search
Notify
Tools
Reply
  
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.
 
Reply With QuoteReport 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
 
Reply With QuoteReport 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
 
Reply With QuoteReport 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, 2003Reply With QuoteReport This Post
<Kalyan>
posted
I used the LAST COMMAND by "kj".. Suited my purpose fine...
Thanks anyway for all your ideas...
 
Reply With QuoteReport 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....
 
Reply With QuoteReport 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, 2003Reply With QuoteReport 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: 188 | Location: pgh pa | Registered: October 06, 2004Reply With QuoteReport 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, 2003Reply With QuoteReport This Post
Platinum Member
posted Hide Post
K. Piippo, very good I like that. That works perfect.
 
Posts: 188 | Location: pgh pa | Registered: October 06, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     concatenating rows of data

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.