IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Sort card to concatenate two records based on one condition


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Thu Apr 23, 2015 2:53 pm
Reply with quote

Hi,

Let me show my requirement below first so that it will be easy to understand.

Code:


Input file1:

1      AAAAAAAA        BBBBBB           CCCCCCC
                       B1B1B1   
                       B2B2B2
                       B3B3B3
2      DDDDDDDD        EEEEEEE          FFFFFFFFFF
                                        F1F1F1F1F
3      GGGGGGG         HHHHHH           IIIIIIIIIII


Input file2:

Code:
AAAAAAAA
DDDDDDDD

Output file expected is:

Code:
 AAAAAAAA        BBBBBB           CCCCCCC
                 B1B1B1   
                 B2B2B2
                 B3B3B3
 DDDDDDDD        EEEEEEE          FFFFFFFFFF
                                  F1F1F1F1F

 


I used Joinkeys in sort card like below:

Code:



//STEP1       EXEC  PGM=SORT
//SORTJNF1  DD     DSN=inputfile1,SISP=SHR
//SORTJNF2  DD     DSN=inputfile2,DISP=SHR
//SORTOUT   DD     DSN=output file, DISP=(NEW,CATLG,DELETE)
//SYSOUT     DD     SYSOUT=*
//SYSIN        DD     *
 SORT FIELDS=COPY
 JOINKEYS FILE=F1,FIELDS=(7,8,A)
 JOINKEYS FILE=F2,FIELDS=(1,8,A)
 REFORMAT FIELDS=(F1:1,133)
 OUTREC FIELDS=(1:1,133)
/*

 


The output that I am getting for the above sort card is :

Code:


1      AAAAAAAA        BBBBBB           CCCCCCC
2      DDDDDDDD        EEEEEEE          FFFFFFFFFF

 


But that was the expected output which I need. Can some please suggest me any idea of how to get the expected output.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Apr 23, 2015 3:10 pm
Reply with quote

You don't show the expected output, so we don't know what you want.

If the data is in order by the key you are using, you should specify SORTED,NOSEQCK on the JOINKEYS where resequencing is not required.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Thu Apr 23, 2015 3:12 pm
Reply with quote

Bill Woodger wrote:
You don't show the expected output, so we don't know what you want.

If the data is in order by the key you are using, you should specify SORTED,NOSEQCK on the JOINKEYS where resequencing is not required.


I have showed the expected output in the first code after the input file2


Expected output :

Code:

Output file expected is:

 AAAAAAAA        BBBBBB           CCCCCCC
                 B1B1B1   
                 B2B2B2
                 B3B3B3
 DDDDDDDD        EEEEEEE          FFFFFFFFFF
                                  F1F1F1F1F

Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Apr 23, 2015 4:09 pm
Reply with quote

OK, I missed that, so I've clarified the Codeing of your question.

You need to use INREC IFTHEN=(WHEN=GROUP on JNF1CNTL to PUSH the key onto each record in the group and use that key as the JOINKEYS key. Your BEGIN would be the key not being blank.

Where you extend the record to include the key-for-all depends on whether your data is fixed-length or variable length.

Then do everything you need in the REFORMAT statement for your output (drop off the key from every record), so you don't need OUTREC FIELDS (see here, by the way) (or INREC BUILD, OUTREC BUILD or OUTFIL BUILD).
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Thu Apr 23, 2015 4:52 pm
Reply with quote

Bill Woodger wrote:
OK, I missed that, so I've clarified the Codeing of your question.

You need to use INREC IFTHEN=(WHEN=GROUP on JNF1CNTL to PUSH the key onto each record in the group and use that key as the JOINKEYS key. Your BEGIN would be the key not being blank.

Where you extend the record to include the key-for-all depends on whether your data is fixed-length or variable length.

Then do everything you need in the REFORMAT statement for your output (drop off the key from every record), so you don't need OUTREC FIELDS (see here, by the way) (or INREC BUILD, OUTREC BUILD or OUTFIL BUILD).


Hi,
Sorry to ask you this but I am new to this IFTHEN and BEGIN i couldnt clearly understand the idea which you gave me. Can you explain me with some codes.

You explained about the JNF1CNTL where should it be included in the sort card containing the JOINKEYS and what value should be given for PUSH and BEGIN should contain the key means, whatever i have specified in the JOINKEYS F1 should I be using that in the BEGIN. how does this compare with input file2 here and the key position differs for the two file as well....
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Thu Apr 23, 2015 6:17 pm
Reply with quote

Quote:
Sorry to ask you this but I am new to this IFTHEN and BEGIN i couldnt clearly understand the idea which you gave me. Can you explain me with some codes.

Swathi,

If you try it, I am sure you'll definitely understand what Bill means.

First try using IFTHEN=(WHEN=GROUP and see what you get as the output. When you see it, you'll know how to use it with JoinKeys
.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Thu Apr 23, 2015 6:23 pm
Reply with quote

RahulG31 wrote:
Quote:
Sorry to ask you this but I am new to this IFTHEN and BEGIN i couldnt clearly understand the idea which you gave me. Can you explain me with some codes.

Swathi,

If you try it, I am sure you'll definitely understand what Bill means.

First try using IFTHEN=(WHEN=GROUP and see what you get as the output. When you see it, you'll know how to use it with JoinKeys
.


Ok I will try but I have no idea what fields should be used in begin push.... will try and let you know. ....
Back to top
View user's profile Send private message
rinsio

New User


Joined: 16 Feb 2015
Posts: 13
Location: Madrid, Spain

PostPosted: Thu Apr 23, 2015 7:25 pm
Reply with quote

Hello Swathi Muralidharan,

Here an approach of your request. But we need the last step: drop off the key in the following records of each group.

Code:

//SORTOUT  DD DSN=XXXXXX.MAINFR.SALIDA,                     
//            DISP=(,CATLG,DELETE),                         
//            SPACE=(TRK,(50,50),RLSE),                     
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=0,DSORG=PS)     
//SYSIN    DD *                                             
    JOINKEYS F1=IN1,FIELDS=(01,8,A)                         
    JOINKEYS F2=IN2,FIELDS=(01,8,A)                   
    REFORMAT FIELDS=(F1:1,80)                               
                                                             
    OPTION COPY                                             
/*                                                           
//JNF1CNTL DD *                                             
    INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,8,CH,NE,C'        '), 
    PUSH=(1:1,8))                                           
/*                                                           



Code:

 BROWSE    XXXXXX.MAINFR.SALIDA                       Line 00000000
 Command ===>                                                  Scrol
********************************* Top of Data **********************
AAAAAAAA       BBBBBB       CCCCCC                                 
AAAAAAAA       B1B1B1                                               
AAAAAAAA       B2B2B2                                               
AAAAAAAA       B3B3B3                                               
DDDDDDDD       EEEEEEE      FFFFFF                                 
DDDDDDDD                    F1F1F1F1                               
******************************** Bottom of Data *******************


Regards
Back to top
View user's profile Send private message
Garry Carroll

Senior Member


Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland

PostPosted: Thu Apr 23, 2015 7:57 pm
Reply with quote

Much better to PUSH the key field into, say, byte 134.
Code:
PUSH=(134:1,8))


The REFORMAT as coded will then drop the bytes after 133.

Garry.
Back to top
View user's profile Send private message
Garry Carroll

Senior Member


Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland

PostPosted: Thu Apr 23, 2015 8:01 pm
Reply with quote

.. sorry, should have mentioned that the JOINKEYS F1 needs adjustment to specify the key in 134,8 since the key to match is now moved.

Garry
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Apr 23, 2015 8:06 pm
Reply with quote

Swathi Muralidharan,

If you're new to the functionality of any part of DFSORT, there is a good "Getting Started" manual available. It contains an example of using a JNFnCNTL file, and, separately, examples of WHEN=GROUP.

Since you are using File 2 as a key-file to extract from, you want you original records unchanged, so put the key from the PUSH in a new location which doesn't exist on your record. After the JOINKEYS, just remove that new key with the REFORMAT.

Variable-length records should be extended at the beginning, and fixed-length records extended at the end.

For fixed-length 80-byte records:
Code:
 PUSH=(81:1,8)


And then:

Code:
 REFORMAT FIELDS=(F1:1,80)


For variable-length records:

Code:
  INREC IFTHEN=(WHEN=NONE,
                BUILD=(1,4,8X,5),


Then:

Code:
 PUSH=(5:1,8)


And

Code:
  REFORMAT FIELDS=(F1:1,4,9)
Back to top
View user's profile Send private message
rinsio

New User


Joined: 16 Feb 2015
Posts: 13
Location: Madrid, Spain

PostPosted: Thu Apr 23, 2015 8:09 pm
Reply with quote

Great Garry,
In the case the record lentgh is 80.

This is the code:

Code:

 //SYSIN    DD *                                               
     JOINKEYS F1=IN1,FIELDS=(73,8,A)                           
     JOINKEYS F2=IN2,FIELDS=(01,8,A)                           
     REFORMAT FIELDS=(F1:1,72)                                 
     OUTREC FIELDS=(1,72,8X)                                   
     OPTION COPY                                               
 /*                                                             
 //JNF1CNTL DD *                                               
     INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,8,CH,NE,C'        '),   
     PUSH=(73:1,8))                                             
 /*                                                             


Code:

 BROWSE    XXXXXX.MAINFR.SALIDA                       Line 00000000 Col 001 080
 Command ===>                                                  Scroll ===> CSR 
********************************* Top of Data **********************************
AAAAAAAA       BBBBBB       CCCCCC                                             
               B1B1B1                                                           
               B2B2B2                                                           
               B3B3B3                                                           
DDDDDDDD       EEEEEEE      FFFFFF                                             
                            F1F1F1F1                                           
******************************** Bottom of Data ********************************


Thanks
Regards
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Thu Apr 23, 2015 8:15 pm
Reply with quote

Bill Woodger wrote:
Swathi Muralidharan,

If you're new to the functionality of any part of DFSORT, there is a good "Getting Started" manual available. It contains an example of using a JNFnCNTL file, and, separately, examples of WHEN=GROUP.

Since you are using File 2 as a key-file to extract from, you want you original records unchanged, so put the key from the PUSH in a new location which doesn't exist on your record. After the JOINKEYS, just remove that new key with the REFORMAT.

Variable-length records should be extended at the beginning, and fixed-length records extended at the end.

For fixed-length 80-byte records:
Code:
 PUSH=(81:1,8)


And then:

Code:
 REFORMAT FIELDS=(F1:1,80)


For variable-length records:

Code:
  INREC IFTHEN=(WHEN=NONE,
                BUILD=(1,4,8X,5),


Then:

Code:
 PUSH=(5:1,8)


And

Code:
  REFORMAT FIELDS=(F1:1,4,9)



Hi,
I tried the code looking IBM Manuals as I want to understand what BEGIN and PUSH and learned about it. I too coded the same way what Rinsio had posted at first but I was giving push value as just 1 instead of that key in the 134th column that made wrong in the code.

My record length is 134 in two input files and output file.

One more doubt like if i give PUSH=(134:1,8) for the blank records in the BEGIN even then the records which are not blank will not have the key value in the 134th column right then how will those records will be included in the output file.
Back to top
View user's profile Send private message
Garry Carroll

Senior Member


Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland

PostPosted: Thu Apr 23, 2015 8:31 pm
Reply with quote

Try it - the PUSH=(134:1,8) will put bytes 1 to 8 from the BEGIN= record into bytes 134-141 of every record until the next record with non-blank in 1,8. The BEGIN= record gets the PUSH value, so will be included in output.

Garry.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Thu Apr 23, 2015 8:37 pm
Reply with quote

Garry Carroll wrote:
Try it - the PUSH=(134:1,8) will put bytes 1 to 8 from the BEGIN= record into bytes 134-141 of every record until the next record with non-blank in 1,8. The BEGIN= record gets the PUSH value, so will be included in output.

Garry.



Yeah ran it now understood how it is working. I misunderstood the concept first....Now its clear....thank you so much for explaining me the concepts. Learning a new concept today thanks a lot.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Thu Apr 23, 2015 8:38 pm
Reply with quote

rinsio wrote:
Great Garry,
In the case the record lentgh is 80.

This is the code:

Code:

 //SYSIN    DD *                                               
     JOINKEYS F1=IN1,FIELDS=(73,8,A)                           
     JOINKEYS F2=IN2,FIELDS=(01,8,A)                           
     REFORMAT FIELDS=(F1:1,72)                                 
     OUTREC FIELDS=(1,72,8X)                                   
     OPTION COPY                                               
 /*                                                             
 //JNF1CNTL DD *                                               
     INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,8,CH,NE,C'        '),   
     PUSH=(73:1,8))                                             
 /*                                                             


Code:

 BROWSE    XXXXXX.MAINFR.SALIDA                       Line 00000000 Col 001 080
 Command ===>                                                  Scroll ===> CSR 
********************************* Top of Data **********************************
AAAAAAAA       BBBBBB       CCCCCC                                             
               B1B1B1                                                           
               B2B2B2                                                           
               B3B3B3                                                           
DDDDDDDD       EEEEEEE      FFFFFF                                             
                            F1F1F1F1                                           
******************************** Bottom of Data ********************************


Thanks
Regards



Thanks a lot Rinsio. I understood the concept behind the code now. Learned a concept today...Thank you....
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Apr 23, 2015 9:17 pm
Reply with quote

Rinsio,

The data is shown as in order. So don't allow the default SORT to occur for each of the JOINKEYS files.

Extend outside the record. Then the entire formatting can be done in REFORMAT. With no need for INREC or OUTREC or OUTFIL BUILD.

See also here.


Code:
 //SYSIN    DD *                                               
     JOINKEYS F1=IN1,FIELDS=(81,8,A),SORTED,NOSEQCK                           
     JOINKEYS F2=IN2,FIELDS=(01,8,A),SORTED,NOSEQCK
     REFORMAT FIELDS=(F1:1,80)                                 
     OPTION COPY                                               
 /*                                                             
 //JNF1CNTL DD *                                               
     INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,8,CH,NE,C'        '),   
                        PUSH=(81:1,8))                                             
 /*
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Thu Apr 23, 2015 9:19 pm
Reply with quote

I have one more doubt like when I send this output file in excel format each record will be created as each row in the Excel right...? But what if I need it to be based on grouping like in excel.

All the Rows corresponding to AAAAAAA in one row with BBBBB, B1B1B1, B2B22B2 all in one column
and DDDDDDDin next row and EEEEE,E1E1E1 in one column of 2nd row.

will i be able to do that.....?
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Thu Apr 23, 2015 10:58 pm
Reply with quote

Quote:
All the Rows corresponding to AAAAAAA in one row with BBBBB, B1B1B1, B2B22B2 all in one column

Do you mean BBBBB, B1B1B2, B2B2B2 under one column or inside one cell?

You can add a delimiter after every field so that they can be grouped under one column. May be a tab (X'05') or a comma (,)
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Thu Apr 23, 2015 11:28 pm
Reply with quote

One more doubt if I send mail of this final output file in comma separated format excel, the comma is added after each field for every row in the file. But actually what I need id the first roe grouped under AAAAAAAA should be in single row in excel. That is, the field values BBBBBB, B1B1B1,B2B2B3 all should in one cell column in that row. Is there any way to get like that....
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Apr 23, 2015 11:57 pm
Reply with quote

Yes, if you bound the fields which contain commas that you want to preserve with, for instance, quotes, then the commas will remain when imported into Excel (or elsewhere).

Code:
AAAAAAA,"BBBBB, B1B1B1, B2B22B2"


That, when imported, should get you two cells, the first containing AAAAAAA and the second BBBBB, B1B1B1, B2B22B2.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Fri Apr 24, 2015 3:38 pm
Reply with quote

Garry Carroll wrote:
Try it - the PUSH=(134:1,8) will put bytes 1 to 8 from the BEGIN= record into bytes 134-141 of every record until the next record with non-blank in 1,8. The BEGIN= record gets the PUSH value, so will be included in output.

Garry.


One more doubt Garry, I want to send this final report in mail as CSV formatted Excel sheet but now what happens is, when I make this report with comma separated, the comma is added after every field in every row. The problem is the second field has BBBB,B1B1B1 and B2B2B2 for the group AAAAA but each comes one row making 3 rows. But how I wanted is to have a single row with first colum containing AAAAA and second column containing BBBBB
B1B1B1
B2B2B2
third column having CCCCC in a single row.

Can you please suggest me any idea on how can I do this. Is there any way....?


Thanks,
Back to top
View user's profile Send private message
Garry Carroll

Senior Member


Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland

PostPosted: Fri Apr 24, 2015 3:52 pm
Reply with quote

Is there a fixed number of rows for each key (e.g. 1,8 = AAAAAAAA )? If not, you'd probably be better processing the report in a program rather than in DFSORT.

There may be a way with sort - as well as starting each GROUP at a non-blank key, is there any way of determining the END of a group (e.g. 16,6,CH,EQ,C' ') ?

Garry.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Fri Apr 24, 2015 4:15 pm
Reply with quote

Garry Carroll wrote:
Is there a fixed number of rows for each key (e.g. 1,8 = AAAAAAAA )? If not, you'd probably be better processing the report in a program rather than in DFSORT.

There may be a way with sort - as well as starting each GROUP at a non-blank key, is there any way of determining the END of a group (e.g. 16,6,CH,EQ,C' ') ?

Garry.


yeah it is fixed. like the first field is the job name which is of 8 char and second will represent the in-condition which is of 8 chars, the third one will have 26 characters, like below:

Code:

jobabcde    job78910       It is example one         ALL
           job12345     which shows job1
jobhhhhh   jobeeeee     It is example two         ALL
                           which shows job2



so here I want like below in the excel sheet when mail
In the first row like below :
first column second column third column fourth column
jobabcde job78910 It is example one ALL
job12345 which shows job1
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Fri Apr 24, 2015 4:20 pm
Reply with quote

RahulG31 wrote:
Quote:
All the Rows corresponding to AAAAAAA in one row with BBBBB, B1B1B1, B2B22B2 all in one column

Do you mean BBBBB, B1B1B2, B2B2B2 under one column or inside one cell?

You can add a delimiter after every field so that they can be grouped under one column. May be a tab (X'05') or a comma (,)


under one cell in a column
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 8
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts JCL sort card - get first day and las... JCL & VSAM 9
No new posts Sort First/last record of a subset th... DFSORT/ICETOOL 7
Search our Forums:

Back to Top