Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
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).
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....
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
.
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. ....
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
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.
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 ********************************
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.
Joined: 08 May 2006 Posts: 1193 Location: Dublin, Ireland
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.
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.
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....
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.
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....
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
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.
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....?
Joined: 08 May 2006 Posts: 1193 Location: Dublin, Ireland
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' ') ?
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