IBM Mainframe Forum

by **javivi** » Thu Nov 07, 2013 2:26 pm

Hi

I have the next problem.

I have a file with records with a key and a date and a amount. I need to repeat all the records in the input by date to date to the next record.

Example, records IN.

Select all

KEY DATE AMOUNT
--- -------- ------
AAA 20130101 001000
AAA 20130103 001500
AAA 20130107 001200

I need to generate the records between record 1 and record 2, between record 2 and 3 ... :

Select all

KEY DATE AMOUNT
--- -------- ------
AAA 20130101 001000
AAA 20130102 001000
AAA 20130103 001500
AAA 20130104 001500
AAA 20130105 001500
AAA 20130106 001500
AAA 20130107 001200

Thanks.

Code'd
and aligned!

by **BillyBoyo** » Thu Nov 07, 2013 2:52 pm

What if there is a single record for a key?

by **javivi** » Thu Nov 07, 2013 3:06 pm

If there is only one record for a key, this must be repeated until the date of the system (the same for the last record of each key).
In the example above, the last register must be repeated until the 20131107.

by **BillyBoyo** » Thu Nov 07, 2013 3:25 pm

Are the first date for all the keys always the same?

Perhaps you can describe the business requirement, what you are trying to do, rather than just how you've decided to do it. We might have other suggestions on how to achieve it.

by **javivi** » Thu Nov 07, 2013 4:09 pm

Ok, I'll tray to explain it.

I have a file with the movements and the balance in money by a customer/s in it/s account/s.
Every day that the customer put or remove money in its account I have a new record with the new balance. Then, I have a record only the days when the customer move money.

The problem is that I need to create the records between dates when the customer not moved money, with the same balance as the previous day

(is understable?)

Sorry for my poor English.
Thanks from Madrid.

by **BillyBoyo** » Thu Nov 07, 2013 5:11 pm

OK, thanks.

So you have many customers, and the first date you have for one customer (start date of being a customer?) can be different for customers? AAA might be 20131101 and BBB might be 20131102? You have dated records for all movements/changes in balance, but you need to have a record for every day with balance as at the previous movement.

It is quite simple in a programming language, COBOL for instance.

For a SORT, a solution may be possible with a table of dates, your main file sorted in date order, JOINKEYS on the dates, then processing in the Main Task of the JOINKEYS to SORT the file into account order, remove the "early" dates (before first date for an account) and populate the balances. However, as well as the relative complexity of the code, it will require sorting the main file twice. It will run slower each day. Whether noticeably day-to-day will depend on the number of accounts on the file. The date range, from oldest date through until today would also impact run time.

If it absolutely, definitely, without option, has to be in SORT, then run this step, dfsort-icetool-icegener/topic9566.html#p47469, and post the sysout from it here please, so we can see your SORT level. Don't expect an instant solution. Kolusu will probably have a better idea, but he's on California time.

by **skolusu** » Thu Nov 07, 2013 7:01 pm

Javivi,

Use the following JCL which will give you the desired results. The trick here is to use the same file twice and use joinkeys to match. The Matching is your key+seqnum created.

For file1 we create the key with seqnum 1 and for file 2 we create the seqnum starting with 0. By doing so we are matching record1 with record2 and record2 with record3 and so on..

Once matched we have the range of dates. We then use DATEDIFF function to find out the difference between those 2 dates. And once we have that it is a case of spliting the records using "/" on OUTFIL. I just showed for difference of 4 , you can code the other ifthen conditions for the rest of days ie. max of 31

Select all

//STEP0100 EXEC PGM=SORT                                        
//SYSOUT   DD SYSOUT=*                                          
//INA      DD *                                                 
----+----1----+----2----+----3----+----4----+----5----+----6----
AAA     20130101  001000                                        
AAA     20130103  001500                                        
AAA     20130107  001200                                        
//INB      DD *                                                 
AAA     20130101  001000                                        
AAA     20130103  001500                                        
AAA     20130107  001200                                        
//SORTOUT  DD SYSOUT=*                                          
//SYSIN    DD *                                                 
  OPTION COPY                                                   
  JOINKEYS F1=INA,FIELDS=(1,3,A,81,2,A),SORTED,NOSEQCK          
  JOINKEYS F2=INB,FIELDS=(1,3,A,81,2,A),SORTED,NOSEQCK          
  REFORMAT FIELDS=(F1:1,80,F2:9,8)                              
  JOIN UNPAIRED,F1                                              
  INREC OVERLAY=(90:81,8,Y4T,DATEDIFF,9,8,Y4T)                  
                                                                
  OUTFIL IFOUTLEN=80,                                           
  IFTHEN=(WHEN=(90,8,SFF,EQ,2),                                 
  BUILD=(1,80,/,                                                
         1,8,9,8,Y4T,ADDDAYS,+1,TOGREG=Y4T,17,64)),             
  IFTHEN=(WHEN=(90,8,SFF,EQ,3),                                 
  BUILD=(1,80,/,                                                
         1,8,9,8,Y4T,ADDDAYS,+1,TOGREG=Y4T,17,64,/,             
         1,8,9,8,Y4T,ADDDAYS,+2,TOGREG=Y4T,17,64)),             
  IFTHEN=(WHEN=(90,8,SFF,EQ,4),                                 
  BUILD=(1,80,/,                                                
         1,8,9,8,Y4T,ADDDAYS,+1,TOGREG=Y4T,17,64,/,             
         1,8,9,8,Y4T,ADDDAYS,+2,TOGREG=Y4T,17,64,/,             
         1,8,9,8,Y4T,ADDDAYS,+3,TOGREG=Y4T,17,64))              
//*                                                             
//JNF1CNTL DD *                                                 
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,3),PUSH=(81:SEQ=2))      
//*                                                             
//JNF2CNTL DD *                                                 
  INREC OVERLAY=(81:SEQNUM,2,ZD,RESTART=(1,3),START=0)          
//*

by **javivi** » Thu Nov 07, 2013 7:46 pm

Thanks Skolusu

I will work in your Idea.
The only problem I can see is when calculating the difference between dates it can be a lot of days (not only until 3 as in your example) for example try this file:
AAA 20130101 001000
AAA 20130103 001500
AAA 20130107 001200
AAA 20130108 001000
AAA 20130903 001500
AAA 20131007 001200

by **BillyBoyo** » Thu Nov 07, 2013 7:48 pm

Yes, but the clarification "In the example above, the last register must be repeated until the 20131107" is problematic. Needs 300 IFTHEN with 300 slash operators, 299, 298 etc.

PS. I was called away, so this is late...

by **skolusu** » Thu Nov 07, 2013 8:42 pm

javivi wrote:Thanks Skolusu

I will work in your Idea.
The only problem I can see is when calculating the difference between dates it can be a lot of days (not only until 3 as in your example) for example try this file:

Javivi,
I only showed you for 3 and mentioned that you need to code the other IFTHEN conditions upto 31 as I assumed that your Dates are within a month. However it seems that you have dates that are spanning across MONTHS. So my solution may not be feasible. What is the max difference between the dates?

IBM Mainframe Forum

Repeat a record

Repeat a record

Re: Repeat a record

Re: Repeat a record

Re: Repeat a record

Re: Repeat a record

Re: Repeat a record

Re: Repeat a record

Re: Repeat a record

Re: Repeat a record

Re: Repeat a record