I have a dataset which i need to select specific rows.
1. Select-
1. If DrAccount or CrAccount contain 9000
eg-
-If the line contain DrAccount 1500 & CrAccount as 9000 check whether it has another row with CrAccount 9000 & DrAccount 1500(like a reversal entry).
-If so select both rows.
-If there are many rows with CrAccount 9000 & DrAccount 1500, select the similar amount & which is most recent by datetime to the main row considering.
-If there are no reversal entry ignore main row.
- Then go to next row, but ignore if the row already selected as reversal entry.
2. If DrAccount or CrAccount not contain 9000
eg-
-If the line contain DrAccount 5000 & CrAccount as 2000 check whether it has another row with CrAccount 9000 & DrAccount 1500(like a reversal entry from 9000).
-If so select both rows.
-If there are many rows with CrAccount 9000 & DrAccount 1500, select the similar amount & which is most recent by datetime to the main row considering.
- If there are no reversal entry ignore main row.
- Then go to next row, but ignore if the row already selected as reversal entry.
Sort - Keep the selected transaction paires as it was selected
Not sure how to create a function to do this. Hope someone can help on this.
My dataset - Attached a CSV file
DateTime Amount Ref DrCode CrCode
01-01-17 16:30 1000 Deduct 1500 9000
01-01-17 16:31 2000 Deduct 1400 9000
02-01-17 08:45 1000 Add 9000 1500
02-01-17 08:45 1000 Add 9000 1400
02-01-17 10:40 50000 Add 9000 3000
02-01-17 16:40 4000 Tranfer 1600 2000
02-01-17 16:41 5000 Tranfer 1700 2000
02-01-17 16:42 9000 Deduct 2000 9000
03-01-17 08:45 4000 Add 9000 1600
03-01-17 08:45 5000 Add 9000 1700
03-01-17 10:48 20000 Deduct 4000 9000
Result I'm expecting is a table similar to this
DateTime Amount Ref DrCode CrCode
01-01-17 16:30 1000 Deduct 1500 9000
02-01-17 08:45 1000 Add 9000 1500
01-01-17 16:31 2000 Deduct 1400 9000
02-01-17 08:45 1000 Add 9000 1400
02-01-17 16:40 4000 Tranfer 1600 2000
02-01-17 16:41 5000 Tranfer 1700 2000
03-01-17 08:45 4000 Add 9000 1600
03-01-17 08:45 5000 Add 9000 1700
1. Select-
1. If DrAccount or CrAccount contain 9000
eg-
-If the line contain DrAccount 1500 & CrAccount as 9000 check whether it has another row with CrAccount 9000 & DrAccount 1500(like a reversal entry).
-If so select both rows.
-If there are many rows with CrAccount 9000 & DrAccount 1500, select the similar amount & which is most recent by datetime to the main row considering.
-If there are no reversal entry ignore main row.
- Then go to next row, but ignore if the row already selected as reversal entry.
2. If DrAccount or CrAccount not contain 9000
eg-
-If the line contain DrAccount 5000 & CrAccount as 2000 check whether it has another row with CrAccount 9000 & DrAccount 1500(like a reversal entry from 9000).
-If so select both rows.
-If there are many rows with CrAccount 9000 & DrAccount 1500, select the similar amount & which is most recent by datetime to the main row considering.
- If there are no reversal entry ignore main row.
- Then go to next row, but ignore if the row already selected as reversal entry.
Sort - Keep the selected transaction paires as it was selected
Not sure how to create a function to do this. Hope someone can help on this.
My dataset - Attached a CSV file
DateTime Amount Ref DrCode CrCode
01-01-17 16:30 1000 Deduct 1500 9000
01-01-17 16:31 2000 Deduct 1400 9000
02-01-17 08:45 1000 Add 9000 1500
02-01-17 08:45 1000 Add 9000 1400
02-01-17 10:40 50000 Add 9000 3000
02-01-17 16:40 4000 Tranfer 1600 2000
02-01-17 16:41 5000 Tranfer 1700 2000
02-01-17 16:42 9000 Deduct 2000 9000
03-01-17 08:45 4000 Add 9000 1600
03-01-17 08:45 5000 Add 9000 1700
03-01-17 10:48 20000 Deduct 4000 9000
Result I'm expecting is a table similar to this
DateTime Amount Ref DrCode CrCode
01-01-17 16:30 1000 Deduct 1500 9000
02-01-17 08:45 1000 Add 9000 1500
01-01-17 16:31 2000 Deduct 1400 9000
02-01-17 08:45 1000 Add 9000 1400
02-01-17 16:40 4000 Tranfer 1600 2000
02-01-17 16:41 5000 Tranfer 1700 2000
03-01-17 08:45 4000 Add 9000 1600
03-01-17 08:45 5000 Add 9000 1700
Attached Files
