Repeat a record



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Repeat a record

Postby 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.

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 ... :

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!
javivi
 
Posts: 47
Joined: Fri Jan 21, 2011 2:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Repeat a record

Postby BillyBoyo » Thu Nov 07, 2013 2:52 pm

What if there is a single record for a key?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Repeat a record

Postby 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.
javivi
 
Posts: 47
Joined: Fri Jan 21, 2011 2:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Repeat a record

Postby 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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Repeat a record

Postby 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.
javivi
 
Posts: 47
Joined: Fri Jan 21, 2011 2:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Repeat a record

Postby 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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Repeat a record

Postby 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

//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)          
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Repeat a record

Postby 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
javivi
 
Posts: 47
Joined: Fri Jan 21, 2011 2:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Repeat a record

Postby 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...
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Repeat a record

Postby 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?
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post