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

How to split the records using the amount field


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

New User


Joined: 12 Jan 2010
Posts: 34
Location: New York

PostPosted: Fri Oct 28, 2016 7:33 pm
Reply with quote

Hi,

I have an amount field in a file and I want to split the records into multiple records 250000 each into one file until the total number of records reaches 10. Even after 10 splits if the amount is greater than zero then write the record into a separate file with the remaining amount.

For ex input file has below data.
Code:
12345 2550000

And I want the output file-1 like this
Code:
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000


Output file-2 like this
Code:
50000


Output file-2 should only be written when the amount field has value greater than zero even after 10 splits.

Please advise.

Thanks,
Ramana.

Code'd correctly
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Fri Oct 28, 2016 8:09 pm
Reply with quote

Is this a one-off for this particular record? If so you would be better off writing a simple program or even doing it manually. If not, will the value to be output on each split always be 250,000? If not, how is that calculated? Suppose you cannot get 10 records from splitting e.g. if your input record was:
Code:
12345   1250000

you would only get 5 splits and nothing in your second dataset (not file).
Back to top
View user's profile Send private message
vnktrrd

New User


Joined: 12 Jan 2010
Posts: 34
Location: New York

PostPosted: Fri Oct 28, 2016 8:28 pm
Reply with quote

Hi Nic,

Yes the split limit is always 250,000. number of splits is based on the total amount. We will have to write to second file only if the amount is greater than 0 after 10 splits. If the amount exhausts after 5 splits, no need to write to second file. If the remaining amount after 10 splits is greater than 25000, we will only write one record for the total amount in the second file. I mean no splits required in the second file.

Thanks,
Ramana.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Fri Oct 28, 2016 10:15 pm
Reply with quote

Here is one way of achieving this.
Code:
//STEP01   EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                     
12345 2550000                                                       
//SORTOUT  DD DSN=&T1,DISP=(,PASS),SPACE=(TRK,(1,1))                 
//SYSIN    DD *                                                     
  SORT FIELDS=COPY                                             
  OUTFIL REPEAT=10,                                           
  IFTHEN=(WHEN=INIT,                                           
  OVERLAY=(15:C'250000',X,SEQNUM,7,ZD,START=250000,INCR=250000,
           22:7,7,ZD,SUB,22,7,ZD,ZD,LENGTH=7,X,SEQNUM,2,ZD))                           
//STEP02   EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD DSN=&T1,DISP=(SHR,PASS)                               
//OUT1     DD SYSOUT=*                                               
//OUT2     DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  SORT FIELDS=COPY                                               
  OUTFIL FNAMES=OUT1,INCLUDE=(22,7,ZD,GE,0),                     
         IFTHEN=(WHEN=(22,7,ZD,LT,15,6,ZD,AND,22,7,ZD,GT,0,AND, 
                       30,2,ZD,LT,10),                           
                 BUILD=(1,6,15,6,/,1,6,23,6)),                   
         IFTHEN=(WHEN=NONE,BUILD=(1,6,15,6))                     
  OUTFIL FNAMES=OUT2,INCLUDE=(22,7,ZD,GT,0,AND,30,2,ZD,EQ,10),   
                 BUILD=(22,7)                                     


EDIT : Apparently my tests were not exhaustive enough. Thanks Bill for notifying me, I have edited my cards above. This assumes original amount to be split >= the individual split amount (250,000 in this case)
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: Fri Oct 28, 2016 11:10 pm
Reply with quote

If you don't have a one-record file, please post representative sample input and output.
Back to top
View user's profile Send private message
vnktrrd

New User


Joined: 12 Jan 2010
Posts: 34
Location: New York

PostPosted: Mon Oct 31, 2016 10:32 am
Reply with quote

Hi Bill,

For example I have give that record. My file contains many of such records with key and amount fields. And amount is not a fixed one. If the amount is greater than 250000 then we should go for split. Else we will write one record and proceed to next. Also, if the amount split goes beyond 10 records then we will write 10 split records in the first file and another record with rest of the amount is written to second file.


Input file
Code:

12345   3000000
12346   2000000
12347   2500000
12348   250000


Output file-1
Code:

12345   250000
12345   250000
12345   250000
12345   250000
12345   250000
12345   250000
12345   250000
12345   250000
12345   250000
12345   250000
12346   250000
12346   250000
12346   250000
12346   250000
12346   250000
12346   250000
12346   250000
12346   250000
12347   250000
12347   250000
12347   250000
12347   250000
12347   250000
12347   250000
12347   250000
12347   250000
12347   250000
12347   250000
12347   250000
12348   250000


Output file-2
Code:

12345   500000
[/code]
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Mon Oct 31, 2016 11:22 am
Reply with quote

vnktrrd,

My previous post was assuming your input had only one-record. Now that you have multiple records like that the solution above does not hold good.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Mon Oct 31, 2016 12:34 pm
Reply with quote

This might be of some interest.
Code:
//STEP01   EXEC PGM=SORT                             
//SYSOUT   DD SYSOUT=*                               
//SORTIN   DD *                                       
12345 3000000                                         
12346 2000000                                         
12347 2500000                                         
12348 0250000                                         
//SORTOUT  DD DSN=&T1,DISP=(,PASS),SPACE=(TRK,(1,1)) 
//SYSIN    DD *                                                   
  SORT FIELDS=COPY                                                 
  OUTFIL REPEAT=10,                                               
  IFTHEN=(WHEN=INIT,                                               
  OVERLAY=(15:C'250000',X,                                         
           SEQNUM,7,ZD,START=250000,INCR=250000,RESTART=(1,5),X,   
           22:7,7,ZD,SUB,22,7,ZD,ZD,LENGTH=7,X,                   
           SEQNUM,2,ZD,RESTART=(1,5)))                             
//STEP02   EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTOUT  DD SYSOUT=*                                         
//SORTIN   DD DSN=&T1,DISP=(SHR,PASS)                           
//OUT1     DD SYSOUT=*                                         
//OUT2     DD SYSOUT=*                                         
//SYSIN    DD *                                                 
  SORT FIELDS=COPY                                             
  OUTFIL FNAMES=OUT1,INCLUDE=(22,7,ZD,GE,0),                   
         IFTHEN=(WHEN=(22,7,ZD,LT,15,6,ZD,AND,22,7,ZD,GT,0,AND,
                       30,2,ZD,LT,10),                         
                 BUILD=(1,6,15,6,/,1,6,23,6)),                 
         IFTHEN=(WHEN=NONE,BUILD=(1,6,15,6))                   
  OUTFIL FNAMES=OUT2,INCLUDE=(22,7,ZD,GT,0,AND,30,2,ZD,EQ,10), 
                 BUILD=(1,6,22,7)       

OUT1
Code:
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12346 250000
12346 250000
12346 250000
12346 250000
12346 250000
12346 250000
12346 250000
12346 250000
12347 250000
12347 250000
12347 250000
12347 250000
12347 250000
12347 250000
12347 250000
12347 250000
12347 250000
12347 250000
12348 250000

OUT2
Code:
12345 0500000
Back to top
View user's profile Send private message
vnktrrd

New User


Joined: 12 Jan 2010
Posts: 34
Location: New York

PostPosted: Mon Oct 31, 2016 1:04 pm
Reply with quote

Hi Arun,

Looks like this is working. I am still testing it tweaking the inputs.

Can you please explain how its achieved ? That might help me map to my data exactly.

Thanks,
Ramana.
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: Mon Oct 31, 2016 3:09 pm
Reply with quote

Run the first step to SYSOUT rather than a named dataset, then you can see what is happening and work out how it is useful.
Back to top
View user's profile Send private message
vnktrrd

New User


Joined: 12 Jan 2010
Posts: 34
Location: New York

PostPosted: Mon Oct 31, 2016 3:22 pm
Reply with quote

Hi Arun,

I have run it and its working fine. Thanks a lot.

All I am confused is the below snippet. Can you please explain how this loop works ?

Code:

OVERLAY=(15:C'249000',X,                                     
         SEQNUM,7,ZD,START=249000,INCR=249000,RESTART=(1,5),X,
         22:7,7,ZD,SUB,22,7,ZD,ZD,LENGTH=7,X,                 
         SEQNUM,2,ZD,RESTART=(1,5)))                         


Thanks,
Ramana.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Mon Oct 31, 2016 8:01 pm
Reply with quote

vnktrrd,

As Bill has pointed out above, you can look at the SORTOUT in the SYSOUT. If you take a look at that, you will see something like below.
Code:
12346 2000000 250000 1750000 01
12346 2000000 250000 1500000 02
12346 2000000 250000 1250000 03
12346 2000000 250000 1000000 04
12346 2000000 250000 0750000 05
12346 2000000 250000 0500000 06
12346 2000000 250000 0250000 07
12346 2000000 250000 0000000 08
12346 2000000 250000 025000} 09
12346 2000000 250000 050000} 10
Each record is repeated 10 times. And an offset to be subtracted from the original amount is created in incremental amounts (column-4 above). I have used the same column to store the subtraction results. So before subtraction, column 4 will look like this
Code:
0250000
0500000
0750000
1000000
1250000
1500000
1750000
2000000
2250000
2500000

So in the example above, we need all records to output-1 as long as the subtraction result is non-negative. Plus if your subtraction result is less than the incremental amount (250000), we are writing the leftover as an additional record. Because it cannot be split further.

We need to write output-2 only when the last record computation result is non-negative. ie, when we have a valid left over amount even after the 10th iteration.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Tue Nov 01, 2016 2:57 am
Reply with quote

Arun,

We can do this in one step and save resources icon_smile.gif

Code:

//STEP0100 EXEC PGM=ICETOOL                                 
//TOOLMSG  DD SYSOUT=*                                       
//DFSMSG   DD SYSOUT=*                                       
//IN       DD *                                             
12345 3000000                                               
12346 2000000                                               
12347 2500000                                               
12348 0250000                                               
//FILE1    DD SYSOUT=*                                       
//FILE2    DD SYSOUT=*                                       
//TOOLIN   DD *                                             
  RESIZE FROM(IN) TO(FILE1) TOLEN(013) USING(CTL1)           
//CTL1CNTL DD *                                             
  INREC OVERLAY=(1,5,X,7,7,                                 
                 1,5,X,7,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,   
                 1,5,X,20,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,   
                 1,5,X,33,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,   
                 1,5,X,46,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,   
                 1,5,X,59,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,   
                 1,5,X,72,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,   
                 1,5,X,85,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,   
                 1,5,X,98,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,     
                 1,5,X,111,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,     
                 1,5,C'~',124,7,ZD,SUB,+250000,LENGTH=7,TO=ZD) 
  OUTFIL FNAMES=FILE1,INCLUDE(7,7,ZD,GT,0,AND,6,1,CH,NE,C'~'), 
         BUILD=(1,5,X,C'250000')                               
  OUTFIL FNAMES=FILE2,INCLUDE(7,7,ZD,GT,0,AND,6,1,CH,EQ,C'~'), 
         OVERLAY=(6:C' ')
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Nov 01, 2016 6:50 am
Reply with quote

magesh23586,

This is a good alternative. But again the number of steps may not always decide if there is any savings or not.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Tue Nov 01, 2016 10:46 am
Reply with quote

Arun Raj wrote:

This is a good alternative. But again the number of steps may not always decide if there is any savings or not.


I agree "not always," but in this case, it is better than your two steps solution.

Your code is little over engineered. You have ten overlay statements with SEQNUM, Restart and three outfil statements with ifthen build.

You are reading the file twice. One pass is always better than two pass.

Bill Woodger, Please advise, if I am wrong.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Nov 01, 2016 4:52 pm
Reply with quote

magesh23586 wrote:
Arun Raj wrote:

This is a good alternative. But again the number of steps may not always decide if there is any savings or not.
You are reading the file twice. One pass is always better than two pass.
You have extended each record 10 times towards the end or record. Depends on what exactly you try to "save", and if we have run sufficient tests to substantiate that.

And what if for some reason in the future, someone decides to split into 50 amounts, you need to extend your record further more towards the right and code additional 40 lines in the OVERLAY
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: Tue Nov 01, 2016 5:32 pm
Reply with quote

My main concern with this topic is the data. Asked for a representative sample, data was only shown which is exactly divisible by 250000 (despite the example in the first post).

If that second set of sample data is representative, then the task is easy, one step, no ICETOOL.

You note that all the values on the first output file are 250000, and on the first OUTFIL output 1-10 records (using the slash-operator) depending on the amount (if greater than 2.5 million, output 10, 2.25 million, output nine, etc).

On the second OUTFIL, INCLUDE= for GT 2.5 million, and subtract 2.5 million to give the residual value.

If, as I suspect, the numbers are not nice and "round", it doesn't take much to make the final one a calculation on the first OUTFIL (subtract the value "below" the one you are testing for).

With all the references to the same fields and constants, I'd definitely show the solution with symbols.

But then, do I want to prepare and test all that, only to have TS/OP explain further that the description of the data is not quite right?

Lots of IFTHENs, lots of code to create, only two calculations at maximum per record.

For a different requirement, I'd go for RESIZE over two passes of the data, but Arun is correct, for any given solution it is only known to perform better or worse than another with the actual data. Generally I'd expect the RESIZE to work better, but no guarantees, as it does depend on what goes along with it, and can, not so much in this example, depend on the data.

As to "over engineering" there's a certain amount of that in both proposed solutions :-)

All suggested solutions would suffer from an increase to 50 splits, but all the code for all the solutions could be "generated" should such a thing arise.

My specific advice is to wait for/obtain clarity from TS/OP before getting to code...
Back to top
View user's profile Send private message
vnktrrd

New User


Joined: 12 Jan 2010
Posts: 34
Location: New York

PostPosted: Wed Nov 02, 2016 11:46 am
Reply with quote

Hi Arun,

A lot of input for this post and a lot of learning for me.

A small help further for your above code I am using for this purpose.

I have mapped the code to my requirement like below.

Code:

SORT FIELDS=COPY                               
OUTFIL REPEAT=10,                               
IFTHEN=(WHEN=INIT,                             
OVERLAY=(105:C'249000',                         
         SEQNUM,7,ZD,START=249000,             
                      INCR=249000,RESTART=(1,6),
         111:28,7,PD,SUB,111,7,ZD,ZD,LENGTH=15,
         SEQNUM,2,ZD,RESTART=(1,6)))           



Here I am overlaying the input record starting from 105 position and my amount field in the input file resides in 28th position in PD format with a length of 7. And, I want the subtracted value in ZD format.

After execution I am not getting the subtracted value correctly. Please suggest if I am doing something wrong.

Thanks,
Ramana.
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: Wed Nov 02, 2016 11:55 am
Reply with quote

When something goes wrong, it is really, really, helpful to show what goes wrong. "It doesn't work" is useless.

Anyway, you sequence number starts at 112, not 111.
Back to top
View user's profile Send private message
vnktrrd

New User


Joined: 12 Jan 2010
Posts: 34
Location: New York

PostPosted: Wed Nov 02, 2016 12:13 pm
Reply with quote

Hi Bill,

The constant 249000 starts at 105 and ends at 110. So the seqnum starts from 111. Please correct if I am wrong.

Output is like this :

Code:

249000000000000052730M01


starts from 105.

Values in amount field : 278304

values after subtraction should be 278304-249000 which is 29304. But I am getting above values with a junk alphabet at the last position.

Thanks,
Ramana.
Back to top
View user's profile Send private message
vnktrrd

New User


Joined: 12 Jan 2010
Posts: 34
Location: New York

PostPosted: Wed Nov 02, 2016 12:40 pm
Reply with quote

I have also tried putting the subtracted value in a separate subsequent position. Its still the same. Subtraction is not working fine here. Please advise what am I am doing wrong.

Code:

249000024900000000000052730M01
249000049800000000000077630M02
249000074700000000000102530M03
249000099600000000000127430M04
249000124500000000000152330M05
249000149400000000000177230M06
249000174300000000000202130M07
249000199200000000000227030M08
249000224100000000000251930M09
249000249000000000000276830M10
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: Wed Nov 02, 2016 2:00 pm
Reply with quote

Yes, on a re-count you are correct. I have a presctiption for new glasses, but I threw it away thinking it was an old receipt from the supermarket...

Your "M" simply indicates you have calculated a negative number. Find the value of M in EBCDIC, the "D" there is the sign, and the number-looking thing is the number.
Back to top
View user's profile Send private message
vnktrrd

New User


Joined: 12 Jan 2010
Posts: 34
Location: New York

PostPosted: Wed Nov 02, 2016 6:51 pm
Reply with quote

Hi,

I still don't why I am negative number. I am subtracting smaller number from greater number. But I am still getting negative values.

Could someone help me ?

Thanks,
Ramana.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Nov 02, 2016 7:16 pm
Reply with quote

vnktrrd,

Apart from the negative sign, the number itself does not seem to be what you expected. Maybe the actual value presented to the computation was different from what you have shown.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Nov 02, 2016 7:24 pm
Reply with quote

Quote:
278304-249000 which is 29304


but the info You posted before shows ---

Code:
249000000000000052730M01



a quick and dirty computation shows that
278304 + 249000 ==> 527304
and that
( -278304 ) - 249000 ==> - 527304
which is exactly what You got
( any decent IT practitioner would have noticed that )
Quote:
I am subtracting smaller number from greater number

Your analysis could not be more wrong ...

subtracting a negative number from a negative number gives a MORE negative number

and a negative number was what You had in some preceding computations

again ... the topic is becoming a waste of time for everybody and it should be locked as I did before
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

 


Similar Topics
Topic Forum Replies
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 Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts EZT program to build a flat file with... All Other Mainframe Topics 9
Search our Forums:

Back to Top