Sort and concatenation based on a group of data



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Sort and concatenation based on a group of data

Postby bsrinu46 » Mon Apr 24, 2017 1:47 am

Hi,

I have an input file as shown below:
001 ABC AA
001 ABC BB
001 ABC CC
001 MNP 11
001 MNP 22
001 MNP AA
001 MNP BB
001 MNP CC
001 MNP DD
001 XYZ 33

I need the output to be grouped based on the first 2 columns and concatenate the 3rd column with commas, as shown in the below format:
001 ABC AA,BB,CC
001 MNP 11,22,AA,BB,CC,DD
001 XYZ 33

Can someone please help with the code for the above sort logic? Thank you!!
bsrinu46
 
Posts: 2
Joined: Mon Apr 24, 2017 1:35 am
Has thanked: 0 time
Been thanked: 0 time

Re: Sort and concatenation based on a group of data

Postby bsrinu46 » Wed Apr 26, 2017 10:44 pm

Hi,

Is this possible using SORT, DFSORT or ICETOOL ? I had been checking few examples with SPLICE, ICETOOL but could not find a solution for this.

if this cannot be accomplished using SORT, I would need to write a COBOL program for it. Need this resolved ASAP. Thank you!!
bsrinu46
 
Posts: 2
Joined: Mon Apr 24, 2017 1:35 am
Has thanked: 0 time
Been thanked: 0 time

Re: Sort and concatenation based on a group of data

Postby Robert Sample » Wed Apr 26, 2017 11:42 pm

Need this resolved ASAP
This is a VOLUNTEER forum - answers are given as people have time, and interest, and knowledge to reply. If you need something resolved ASAP, you need to convince your management to pay for a consultant to assist you. Replies on a forum may come after hours ... or days ... or weeks ... or months ... or never. And pushing for information the way you have tends to REDUCE the chances of your receiving a reply, since which volunteer wants you harassing them?
Robert Sample
Global moderator
 
Posts: 3719
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 279 times

Re: Sort and concatenation based on a group of data

Postby Aki88 » Thu Apr 27, 2017 12:19 pm

Hello,

It is very much possible using DFSORT; but it requires a bit of programming which is directly dependent on your DFSORT coding proficiency.

You need to first answer:
a. How many 'third column' are possible under a unique first and second column group?
b. Do you want the output values to be arranged in a SORTED order, or they need to be arranged as-is?

The above questions are asked to ascertain the maximum record length possible should there be many values under column 3 for a unique group 1/2; you would've already noticed by now that your o/p's data length is not same for all records.

The pseudo-logic would go on the lines of:
a. Use INREC/IFTHEN with INIT, and pad the input record on a column at the maximum assumed record length of the output expected; now this is the data on which modifications will be carried out
b. Now group all the records and pad a unique id for each group and their subsequent column 3 values, using INREC/IFTHEN/GROUP with PUSH/ID/SEQ
c. Once the records are grouped together, use INREC/IFTHEN/GROUP with BEGIN on SEQ number AND BUILD to build each column 3 value with the grouped records.
d. Lastly, couple them with OUTFIL NODETAIL SECTIONS TRAILER3 to write the output

If you want a sample code, look at this post; or google - 'combine multiple rows into one dfsort'.

Reiterating, a bit of code is required to achieve this task, and will directly depend upon your programming proficiency.
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post