Page 1 of 1

Sort and concatenation based on a group of data

PostPosted: Mon Apr 24, 2017 1:47 am
by bsrinu46
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!!

Re: Sort and concatenation based on a group of data

PostPosted: Wed Apr 26, 2017 10:44 pm
by bsrinu46
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!!

Re: Sort and concatenation based on a group of data

PostPosted: Wed Apr 26, 2017 11:42 pm
by Robert Sample
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?

Re: Sort and concatenation based on a group of data

PostPosted: Thu Apr 27, 2017 12:19 pm
by Aki88
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.