Rearrange records without effecting other records

murmohk1 · Posted: Wed Nov 29, 2006 8:22 am

Hi,

I have a flat file with the following layout -

(1) Policy# (12 bytes)
(2) Date (ddmmyy format)
(3) Remaining data (given as ????)

In the file, we have set of records with the same policy# but with different set of dates like......

IP file Example-

1234567890abc 120506 ???????
1234567890abc 140506 ???????
5678123456xyz 140506 ???????
1234567890abc 130506 ???????

My requirement is to re-arrange the records as per the date but without sorting on any of the field. i.e, other records place shouldn't change.

OP file should be -

1234567890abc 120506 ???????
1234567890abc 130506 ??????? --- this was 4 record in ip file
5678123456xyz 140506 ???????
1234567890abc 140506 ??????? --- this was record #2 in ip file

Is it possible to acheive this using DFSORT? If yes, please provide the code.

Thanks & Regards,
Mohan

guptae · Posted: Wed Nov 29, 2006 9:39 am

Hi Mohan,

If i/p file like

IP file Example-

1234567890abc 120506 ???????
1234567890abd 140506 ???????
5678123456xyz 140506 ???????
1234567890abc 130506 ???????
Then tell me what should be the output

jeetpratap · New User Joined: 21 Jul 2006 Posts: 5

Hi

IP file Example-

1234567890abc 120506 ???????
1234567890abc 140506 ???????
5678123456xyz 140506 ???????
1234567890abc 130506 ???????

My requirement is to re-arrange the records as per the date but without sorting on any of the field. i.e, other records place shouldn't change.

OP file should be -

1234567890abc 120506 ???????
1234567890abc 130506 ??????? --- this was 4 record in ip file
5678123456xyz 140506 ???????
1234567890abc 140506 ??????? --- this was record #2 in ip file

as per the example quoted by you, you are not even sorting the records on Dates. otherwise, the output should have been

1234567890abc 120506 ???????
1234567890abc 130506 ???????
1234567890abc 140506 ???????
5678123456xyz 140506 ???????

But in your example, WHY IS 2ND RECORD PLACED AT 4TH POSTION?

PLEASE CLARIFY1

REGARDS

murmohk1 · Posted: Wed Nov 29, 2006 3:03 pm

The date represents transaction date for that policy and the records which has the same date (say 12th) has to come along with other 12th transacted policies.

Because of some reason, few policies are getting stored in random order like 12th followed by 14th and 13th.

My requirement is to rearrange all those policies which have random date order with out effeting the other records/policies.

I hope Im clear to all.

murmohk1 · Posted: Wed Nov 29, 2006 8:27 pm

Also I would like to provide more info which may be helpful for you people -

Total # of records - Around 1.4 M (7 flat files each having 0.2M records)

And we identified 17 policies which has the above problem (stored in random date order).

Regards,
Mohan

William Thompson · Posted: Wed Nov 29, 2006 9:47 pm

Just sort on the date but use the equals parameter so when the sort key is equal sort will keep the original sequence intact.

Frank Yaeger · Posted: Wed Nov 29, 2006 10:09 pm

William is correct. You want to SORT by the date but use the EQUALS parameter to ensure that records with the same date are kept in their original order. You can use these DFSORT control statements:

murmohk1 · Posted: Thu Nov 30, 2006 8:28 am

Thanks Williams and Frank for your quick response.

Regards,
Mohan