Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.