|
View previous topic :: View next topic
|
| Author |
Message |
saravanakmr.tpr
New User
Joined: 16 Feb 2012
Posts: 9
Location: India
|
|
|
|
Hi,
I need to process a file which has below format
akey1 bkey1 A adate1
akey1 bkey1 A adate2
akey1 bkey2 A adate1
akey1 bkey2 A adate2
akey1 bkey3 A adate3
akey2 bkey1 A adate1
akey3 bkey1 A adate1
akey3 bkey2 A adate2
akey1 bkey1 D ddate1
akey1 bkey1 D ddate2
akey1 bkey1 D ddate3
akey4 bkey1 D ddate1
akey2 bkey1 D ddate1
......
Now i need the output file to be in the below format
akey1 bkey1 A adate1 akey1 bkey1 D ddate1
akey1 bkey1 A adate2 akey1 bkey1 D ddate2
akey1 bkey1 A adate1 akey1 bkey1 D ddate3
akey2 bkey1 A adate1 akey2 bkey1 D ddate1
........
Under the following conditions this reformatting has to be done:
1: Delete date(ddate) against any Add date(adate) in the output record must be greater than add date.
2: There can be no ddate or adate for a particular key. In that case only the adate rec or ddate rec whichever exists has to be written in the output file.
3:There can be any number of adates and ddates for a particular key
4: I can use only SYNCSORT not DFSORT
Can anyone pls help me to resolve this..? |
|
| Back to top |
|
 |
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL
|
|
|
|
| saravanakmr.tpr wrote: |
Hi,
I need to process a file which has below format
akey1 bkey1 A adate1
akey1 bkey1 A adate2
akey1 bkey2 A adate1
akey1 bkey2 A adate2
akey1 bkey3 A adate3
akey2 bkey1 A adate1
akey3 bkey1 A adate1
akey3 bkey2 A adate2
akey1 bkey1 D ddate1
akey1 bkey1 D ddate2
akey1 bkey1 D ddate3
akey4 bkey1 D ddate1
akey2 bkey1 D ddate1
......
Now i need the output file to be in the below format
akey1 bkey1 A adate1 akey1 bkey1 D ddate1
akey1 bkey1 A adate2 akey1 bkey1 D ddate2
akey1 bkey1 A adate1 akey1 bkey1 D ddate3
akey2 bkey1 A adate1 akey2 bkey1 D ddate1
........
Under the following conditions this reformatting has to be done:
1: Delete date(ddate) against any Add date(adate) in the output record must be greater than add date.
2: There can be no ddate or adate for a particular key. In that case only the adate rec or ddate rec whichever exists has to be written in the output file.
3:There can be any number of adates and ddates for a particular key
4: I can use only SYNCSORT not DFSORT |
5. I can only post in the DFSORT forum, not the Syncsort/JCL forum. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello and welcome to the forum,
Suggest you may get more/better replies if you show something that looks like the real data - both input and output.
What are the recfm and lrecl for the files? |
|
| Back to top |
|
|
saravanakmr.tpr
New User
Joined: 16 Feb 2012
Posts: 9
Location: India
|
|
|
|
following is the input:
| Code: |
3CRIMSCKAN EXT TEMPLATE 4 1302010-05-15-04.42.36.485629D
3CRIMSCKAN EXT TEMPLATE 4 1302009-07-07-01.21.50.519386A
3CRIMSCKAN EXT TEMPLATE 4 1302009-07-07-01.21.50.519386A
3CRNPNEROS PASTEST2 3502010-08-05-17.05.10.928641D
3CRNPNEROS PASTEST2 3502010-02-12-14.48.01.261729A
3CRNPNEROS PASTEST2 3502010-02-12-14.48.01.261729A
3CRNPNEROS PASTEST2 3502010-08-05-17.05.10.928641D
3CRNPNEROS PASTEST2 3502010-02-12-14.48.01.261729A
3CRNPNEROS PASTEST2 3502010-08-05-17.05.10.928641D
3CRNPNEROS PASTEST2 3502010-08-05-17.05.10.928641D |
"Code'd"
RECFM: FB, LRECL: 63
this file has 4 key fields,
key1- 1,3
key2- 4,15
key3- 19,15
key4- 34,3
the order shuld be just as i have written.
output file should be
3CRIMSCKAN EXT TEMPLATE 4 1302009-07-07-01.21.50.519386A3CRIMSCKAN EXT TEMPLATE 4 1302010-05-15-04.42.36.485629D
(del date is greateer than add date)
and do consider the otr conditions also that i mentioned earlier. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Please provide a more clear explanation.
I do not understand how the individual data items relate to the others of the same "key".
Looking at the data i believe i can see the pattern, but how should the "rule" be defined? Imagine this was code - how would you document what to do?
What should happen when there are (say 20) items for a key to be selected? |
|
| Back to top |
|
|
saravanakmr.tpr
New User
Joined: 16 Feb 2012
Posts: 9
Location: India
|
|
|
|
For the same user I have many add dates and delete dates.
like one user got added in the month 07/2012 and got deleted in 08/2012
and the same user has got added again in the month 09/2012 got deleted in 10/2012. in that case output should have 2 records
userid adddate(07/2012) deldate(08/2012)
userid adddate(09/2012) deldate(10/2012)
Also i may not have add dates or del dates for a particular user
in that case i should write that record as it is
userid adddate(or)deldate <whatever the input file has>
If i have uneven number of add and del dates for a user like 2 add dates (01/2012), (5/2012) and 3 del dates (11/2011), (02/2012), (6/2012)
then the out should be like
userid 11/2012 D
userid 01/2012 A 02/2012 D
userid 05/2012 A 06/2012 D
and der can be 3 add dates and 2 del dates also.
Hope, u can get a clear idea now.. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| Hope, u can get a clear idea now.. |
I believe i can see the pattern, but you have not explained the rule of how to "tie" groups of records for the same id together. You have explained what you want as output, but not how to deal with the records to get the output.
Looking at the "input" you "know" how the output should be, but we need to have this expressed in processing terms rather than only the output. |
|
| Back to top |
|
|
saravanakmr.tpr
New User
Joined: 16 Feb 2012
Posts: 9
Location: India
|
|
|
|
| Quote: |
| rule of how to "tie" groups of records for the same id together |
I think I have already explained how to deal with the records. Anyway i'l try to give you some more points which i think will be helpful.
Now u r asking, if der are say 4 recs for the same key how to process those right..?
One of the way that i can think of is like splitting the Add date recs and del date recs into two files and joining them in the next step and removing the unwanted recs. But in this method the resulting no. of recs is the cartesian product of the no. recs in the two files. In that I faced the following problem
userid 2011/08/11 A 2011/09/15 D
userid 2012/05/21 A 2011/09/15 D
userid 2011/08/11 A 2012/07/09 D
userid 2012/05/21 A 2012/07/09 D
in the above set actually the desired output is only the last and first rec. other two is the result of joinin(cartesian product). In the other two i can easily eliminate the one in which add date is greater than del date(2nd rec). but I'm not able to find the 3rd one exactly and remove it. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I think I have already explained how to deal with the records. |
You have explained this in a way that You understand. Unfortunately, to get help you need to explain it so that everyone else can understand. . .
Suggest you post the logical steps you want used to process the input so the desired output will be created.
Why have you not been consistent and used the sample input data records for the later "examples"?
It will be helpful if in your reply you expand the sample input (to show more of the cases that might occur), show the desired output from the expanded input, and then the "ruloes" for processing in sentence form.
When the new info is posted, i'll go back and remove most of the replies that got to this point as i believe they will not be useful ondce we have a complete request. |
|
| Back to top |
|
|
saravanakmr.tpr
New User
Joined: 16 Feb 2012
Posts: 9
Location: India
|
|
|
|
ok.
1. I have the Add dates in the first file. and it looks like..
| Code: |
AACPAACSDEV 780A2012-01-30-11.24.51.226137
AB1PAB1AKLU 780A2011-10-26-14.55.33.456229
AB1PAB1CDEB 780A2011-10-26-11.04.02.554112
AB1PAB1JGON 780A2012-01-12-10.16.22.967238
AB1PAB1KGLY 780A2011-12-15-14.19.44.503711
AB1PAB1AKIN 780A2012-05-10-10.24.43.268403
AB1PAB1BROD 780A2012-02-03-16.07.14.565492
AB1PAB1CNAU 780A2012-01-05-09.26.40.820278
AB1PAB1DCHI 780A2012-03-07-09.01.55.641483
AB1PAB1DDAI 780A2012-03-19-11.42.41.474049
AB1PAB1DELU 780A2012-01-25-15.38.54.469958
AB1PAB1DNOV 780A2011-10-26-13.01.20.396750
AB1PAB1DSUL 780A2011-09-12-10.39.33.322986
AB1PAB1EBOY 780A2012-06-22-10.10.02.222185
AB1PAB1GHAI 780A2012-04-20-15.18.43.605846
AB1PAB1LNEL 780A2012-06-21-12.22.31.512531
AB1PAB1MMEH 780A2012-03-19-11.46.25.793824
AB1PAB1MMUT 780A2012-01-11-10.54.40.557388
AB1PAB1PSAU 780A2012-01-05-11.30.36.191129
|
2. In the second file I have delete dates
| Code: |
AACPAACFDIG 780D2011-09-14-11.12.10.149155
AACPAACSISO 780D2012-06-23-04.45.29.278399
AACPAACTIPA 780D2012-05-26-04.49.52.808873
ABAPABACSTO 782D2011-11-19-05.39.09.580214
ABAPABATLAH 782D2011-07-16-04.39.05.943715
AB1PAB1ACOL 780D2011-09-22-11.37.30.342719
AB1PAB1AJOL 780D2012-07-24-08.36.00.557590
AB1PAB1AKLU 780D2011-10-26-14.55.33.456229
AB1PAB1AKLU 780D2011-10-25-11.30.53.997235
AB1PAB1AMAR 780D2012-06-02-04.42.42.594714
AB1PAB1APOL 780D2011-08-12-16.12.38.114839
AB1PAB1APOL 780D2011-11-23-10.37.35.469199
AB1PAB1ASCH 782D2011-09-26-10.13.43.994270
AB1PAB1BARR 780D2011-12-21-10.31.23.814329
AB1PAB1BCHA 780D2012-03-16-10.25.38.280788
AB1PAB1BROW 780D2012-02-18-05.23.13.030652
AB1PAB1BSIA 780D2011-08-06-04.41.10.443595
AB1PAB1BSIA 780D2012-07-11-16.49.53.113952
AB1PAB1CCIA 780D2011-12-21-10.44.07.164793
|
3. From col 1 to 21 is the key field, at 22nd i have add/del indicator followed by the timestamp.
4. In the first step I do a join based on the key field and get 3 kinds of output datasets
(i) It has matched records from add date and del date files and the o/p looks like...
| Code: |
AB1PAB1ACOL 780A2011-09-22-11.37.30.342719 D2011-09-22-11.37.30.34
AB1PAB1AKLU 780A2011-10-26-14.55.33.456229 D2011-10-26-14.55.33.45
AB1PAB1AKLU 780A2011-10-26-14.55.33.456229 D2011-10-25-11.30.53.99
AB1PAB1BCHA 780A2012-03-16-10.25.38.280788 D2012-03-16-10.25.38.28
AB1PAB1CDEB 780A2011-10-26-11.04.02.554112 D2011-10-26-11.04.02.55
AB1PAB1CDEL 780A2011-10-20-15.55.09.216339 D2011-08-30-17.50.54.46
AB1PAB1CDEL 780A2011-10-20-15.55.09.216339 D2011-10-20-15.55.09.21
AB1PAB1DBOB 780A2011-11-15-09.46.21.906975 D2011-11-15-09.46.21.90
AB1PAB1DBRU 780A2011-08-10-09.25.47.987758 D2011-08-10-09.25.47.98
AB1PAB1DDAI 780A2012-03-19-11.42.41.474049 D2012-03-19-11.33.48.82
AB1PAB1FRAN 780A2012-06-20-08.59.51.368371 D2012-06-20-08.59.51.36
AB1PAB1GPOR 780A2012-06-14-09.42.48.358314 D2012-06-14-09.42.48.35
AB1PAB1JCHA 780A2011-11-15-10.04.07.148642 D2011-11-15-10.04.07.14
AB1PAB1JGAM 780A2011-12-07-10.52.50.578546 D2011-12-07-10.52.50.57
AB1PAB1JGON 780A2012-01-12-10.16.22.967238 D2012-01-12-10.16.22.96
AB1PAB1JMAR 782A2012-06-12-10.22.07.624950 D2012-06-12-10.22.07.62
AB1PAB1KGLY 780A2011-12-15-14.19.44.503711 D2011-12-15-14.19.44.50
AB1PAB1KKUR 780A2011-09-01-15.03.19.838750 D2011-09-01-15.03.19.83
AB1PAB1LNEL 780A2012-06-21-12.22.31.512531 D2011-07-30-04.43.14.39
|
(ii) In the second file I have unmatched recs from add dates file alone.
| Code: |
AACPAACSDEV 780A2012-01-30-11.24.51.226137
ABAPABABHIL 782A2012-05-02-08.35.25.767261
ABAPABAHCOF 782A2012-01-04-14.40.08.626963
ABAPABAMCAR 782A2011-10-31-10.13.32.932685
AB1PAB1ABAB 782A2012-02-27-08.57.04.490770
AB1PAB1AKIN 780A2012-05-10-10.24.43.268403
AB1PAB1AMAN 780A2012-01-26-09.05.21.129123
AB1PAB1BROD 780A2012-02-03-16.07.14.565492
AB1PAB1CNAU 780A2012-01-05-09.26.40.820278
AB1PAB1DCHI 780A2012-03-07-09.01.55.641483
AB1PAB1DELU 780A2012-01-25-15.38.54.469958
AB1PAB1DNOV 780A2011-10-26-13.01.20.396750
AB1PAB1DSUL 780A2011-09-12-10.39.33.322986
AB1PAB1EBOY 780A2012-06-22-10.10.02.222185
AB1PAB1GHAI 780A2012-04-20-15.18.43.605846
AB1PAB1HMUT 782A2012-05-10-09.53.00.576094
AB1PAB1JCLA 780A2011-09-12-10.28.13.448350
AB1PAB1KWIL 782A2011-11-07-15.09.16.374519
AB1PAB1LMCL 780A2012-07-20-09.42.53.405271
|
(iii) In the 3rd file unmatched recs from del dates file alone.
| Code: |
AACPAACFDIG 780D2011-09-14-11.12.10.149155
AACPAACSISO 780D2012-06-23-04.45.29.278399
AACPAACTIPA 780D2012-05-26-04.49.52.808873
ABAPABACSTO 782D2011-11-19-05.39.09.580214
ABAPABATLAH 782D2011-07-16-04.39.05.943715
AB1PAB1AJOL 780D2012-07-24-08.36.00.557590
AB1PAB1AMAR 780D2012-06-02-04.42.42.594714
AB1PAB1APOL 780D2011-08-12-16.12.38.114839
AB1PAB1APOL 780D2011-11-23-10.37.35.469199
AB1PAB1ASCH 782D2011-09-26-10.13.43.994270
AB1PAB1BARR 780D2011-12-21-10.31.23.814329
AB1PAB1BROW 780D2012-02-18-05.23.13.030652
AB1PAB1BSIA 780D2011-08-06-04.41.10.443595
AB1PAB1BSIA 780D2012-07-11-16.49.53.113952
AB1PAB1CCIA 780D2011-12-21-10.44.07.164793
AB1PAB1CCIA 780D2011-10-20-15.58.27.981376
AB1PAB1COOK 780D2011-10-08-04.40.22.549513
AB1PAB1CWHE 780D2012-03-27-11.01.13.433127
AB1PAB1DBLI 780D2011-12-06-16.27.40.728724
|
4. Now i have no issues with 2nd and 3rd file i can directly write recs from these to an o/p file.
5. In the first file only I have a problem. Now I'll tel what exactly the o/p should be. From the two i/p files that I have I need to match the recs based on the key field then I have to analyze the dates that I have got put against each other.
(i) consider this key 'AB1PAB1AKLU 780', I have only one entry in add file for this and two entry in del file while joining i'm getting the o/p as
| Code: |
AB1PAB1AKLU 780A2011-10-26-14.55.33.456229 D2011-10-26-14.55.33.45
AB1PAB1AKLU 780A2011-10-26-14.55.33.456229 D2011-10-25-11.30.53.99
|
in this i have to retain only the 1st rec and eliminate the 2nd since the add date is greater than the del date. This may be I can accomplish by doing a simple comparison.
(ii) But this does not work if there are 2 add as well as 2 del date recs.
For the key field '3CRPAADDLEW 130', I have two add dates and 2 del dates while joinin them i get the below o/p
| Code: |
3CRPAADDLEW 130 2009-07-07-01.21.50.519386A2009-11-28-04.40.11.941057D
3CRPAADDLEW 130 2009-07-07-01.21.50.519386A2011-03-09-10.24.13.221239D
3CRPAADDLEW 130 2010-02-11-11.47.24.574242A2009-11-28-04.40.11.941057D
3CRPAADDLEW 130 2010-02-11-11.47.24.574242A2011-03-09-10.24.13.221239D
|
Now the required o/p is 1st and last recs. As i said in the previous step I can easily remove the 3rd rec. But how can I eliminate the 2nd rec?? and consider the same scenario for more than 2 add and del dates also.
Looking forward for your reply... |
|
| Back to top |
|
|
saravanakmr.tpr
New User
Joined: 16 Feb 2012
Posts: 9
Location: India
|
|
|
|
| Any replies... pls....!!! |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
As no one has proposed sort control statements to accomplish this, it may be time to consider actually writing code in whatever language is supported by your environment. |
|
| Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
| Quote: |
| Any replies... pls....!!! |
This is very complex problem.
Far too complex for me or anyone else to do on our own time for free.
This is why you have not received any replies.
Requesting replies again is completely out of line and will draw the rath of many seniors on this forum.
I will however be willing to provide you a solution for the appropriate fee.
I work cheaper than most on this forum, and accept Paypal for payment.
Please PM me for further information. |
|
| Back to top |
|
|
|
|
