Hello,
I have trouble defining a condition in to my script. I have Loaded a table from a single source and I need to filter it based on the below condition.
Within the same serial number(that is serial + suffix set). Look for the First Lost Charges entry, then look within the suffix where that charge occurred and if there is a Lost Credits entry - ignore/continue to the next Lost charges, if there is no Lost Credits entry for that suffix, but there is a Lost Charges - load all the follow up data for that serial including the Lost Charges (not just that suffix).
Example Data:
| Serial |
Sfx |
Ser|Sfx |
Value |
Charge Date |
Charge Type |
| 96 |
1 |
96|1 |
3.50 |
30/09/2002 |
Rental Charges |
| 96 |
1 |
96|1 |
3.50 |
31/10/2002 |
Rental Charges |
| 96 |
1 |
96|1 |
3.50 |
30/11/2002 |
Rental Charges |
| 96 |
1 |
96|1 |
3.50 |
31/12/2002 |
Rental Charges |
| 96 |
1 |
96|1 |
3.50 |
31/01/2003 |
Rental Charges |
| 96 |
1 |
96|1 |
3.50 |
28/02/2003 |
Rental Charges |
| 96 |
1 |
96|1 |
3.50 |
31/03/2003 |
Rental Charges |
| 96 |
1 |
96|1 |
3.50 |
30/04/2003 |
Rental Charges |
| 96 |
1 |
96|1 |
3.50 |
31/05/2003 |
Rental Charges |
| 96 |
1 |
96|1 |
3.50 |
30/06/2003 |
Rental Charges |
| 96 |
1 |
96|1 |
3.50 |
31/07/2003 |
Rental Charges |
| 96 |
1 |
96|1 |
3.50 |
31/08/2003 |
Rental Charges |
| 96 |
1 |
96|1 |
112.50 |
14/10/2003 |
Lost Charges |
| 96 |
2 |
96|2 |
3.50 |
30/11/2003 |
Rental Charges |
| 96 |
2 |
96|2 |
3.50 |
31/12/2003 |
Rental Charges |
| 96 |
2 |
96|2 |
3.50 |
31/01/2004 |
Rental Charges |
| 96 |
3 |
96|3 |
3.50 |
31/08/2005 |
Rental Charges |
| 96 |
3 |
96|3 |
3.50 |
30/09/2005 |
Rental Charges |
| 96 |
3 |
96|3 |
3.50 |
31/10/2005 |
Rental Charges |
| 96 |
4 |
96|4 |
3.50 |
31/01/2006 |
Rental Charges |
| 96 |
4 |
96|4 |
3.50 |
28/02/2006 |
Rental Charges |
| 96 |
4 |
96|4 |
112.50 |
10/05/2006 |
Lost Charges |
| 96 |
4 |
96|4 |
-112.50 |
15/05/2006 |
Lost Credits |
Resulting data should be:
| Serial |
Sfx |
Ser|Sfx |
Value |
Charge Date |
Charge Type |
| 96 |
1 |
96|1 |
112.50 |
14/10/2003 |
Lost Charges |
| 96 |
2 |
96|2 |
3.50 |
30/11/2003 |
Rental Charges |
| 96 |
2 |
96|2 |
3.50 |
31/12/2003 |
Rental Charges |
| 96 |
2 |
96|2 |
3.50 |
31/01/2004 |
Rental Charges |
| 96 |
3 |
96|3 |
3.50 |
31/08/2005 |
Rental Charges |
| 96 |
3 |
96|3 |
3.50 |
30/09/2005 |
Rental Charges |
| 96 |
3 |
96|3 |
3.50 |
31/10/2005 |
Rental Charges |
| 96 |
4 |
96|4 |
3.50 |
31/01/2006 |
Rental Charges |
| 96 |
4 |
96|4 |
3.50 |
28/02/2006 |
Rental Charges |
| 96 |
4 |
96|4 |
112.50 |
10/05/2006 |
Lost Charges |
| 96 |
4 |
96|4 |
-112.50 |
15/05/2006 |
Lost Credits |
Any help appreciated.
