E
esimbo
Hi
I have been tasked with producing a new input file which requires some
manipulation of a file to generate a unique ID. I have been advised
that Perl will be the simplest course of action here but in all
honesty, I'm not sure where to start.
My input file contains the following snippets of data.
Date, Amount, Refno
2005/01/07, 00000.096532030000,#0000015511
2005/06/07, 00006.963788280000,#0000015511
2005/06/13, 00002.243425000000,#0000030502
2006/06/16, 00002.243425000000,#0000030502
2006/06/16, 00047.230000000000,#0000030502
2005/02/18, 00002.243425000000,#0000040505
2005/02/13, 00001.738765000000,#0000030627
Based on this file, I need to generate a new file containing the same
fields but with an added column for the Unique id.
The premise is simple. Check the refno column and match against that
value against the corresponding value in the next row. If they both
match, then apend append both "I" and the Date to the Refno to generate
the ID. It then iterates through the rows repeating the same step until
it reaches the last occurence of the Refno. When we reach the last
occurence of the Refno, i.e we start a new Refno sequence, in which
case we append a "P".
Therefore, using the sample above, the result I would expect is as
follows
ID,Date,Amount, Refno
0000015511_I_2005/01/07, 2005/01/07, 00000.096532030000,#0000015511
0000015511_P_2005/06/07, 2005/06/07, 00006.963788280000,#0000015511
0000030502_I_2005/06/13, 2005/06/13, 00002.243425000000,#0000030502
0000030502_I_2006/06/16, 2006/06/16, 00002.243425000000,#0000030502
0000030502_P_2005/06/16, 2006/06/16, 00047.230000000000,#0000030502
0000030505_P_2005/02/18, 2005/02/18, 00002.243425000000,#0000040505
0000030627_P_2005/02/13, 2005/02/13, 00001.738765000000,#0000030627
If anyone can provide any assistance here, I'd really be grateful.
Regards.
I have been tasked with producing a new input file which requires some
manipulation of a file to generate a unique ID. I have been advised
that Perl will be the simplest course of action here but in all
honesty, I'm not sure where to start.
My input file contains the following snippets of data.
Date, Amount, Refno
2005/01/07, 00000.096532030000,#0000015511
2005/06/07, 00006.963788280000,#0000015511
2005/06/13, 00002.243425000000,#0000030502
2006/06/16, 00002.243425000000,#0000030502
2006/06/16, 00047.230000000000,#0000030502
2005/02/18, 00002.243425000000,#0000040505
2005/02/13, 00001.738765000000,#0000030627
Based on this file, I need to generate a new file containing the same
fields but with an added column for the Unique id.
The premise is simple. Check the refno column and match against that
value against the corresponding value in the next row. If they both
match, then apend append both "I" and the Date to the Refno to generate
the ID. It then iterates through the rows repeating the same step until
it reaches the last occurence of the Refno. When we reach the last
occurence of the Refno, i.e we start a new Refno sequence, in which
case we append a "P".
Therefore, using the sample above, the result I would expect is as
follows
ID,Date,Amount, Refno
0000015511_I_2005/01/07, 2005/01/07, 00000.096532030000,#0000015511
0000015511_P_2005/06/07, 2005/06/07, 00006.963788280000,#0000015511
0000030502_I_2005/06/13, 2005/06/13, 00002.243425000000,#0000030502
0000030502_I_2006/06/16, 2006/06/16, 00002.243425000000,#0000030502
0000030502_P_2005/06/16, 2006/06/16, 00047.230000000000,#0000030502
0000030505_P_2005/02/18, 2005/02/18, 00002.243425000000,#0000040505
0000030627_P_2005/02/13, 2005/02/13, 00001.738765000000,#0000030627
If anyone can provide any assistance here, I'd really be grateful.
Regards.