Hi All,
Kind of an unusual one ( or I hope not ). Our internal expense system is only able to output a flat file for a full extract of all claims but that creates duplicate numeric values as effectively its a one to many relationship in the extract.
| Report ID | EMPLOYEE_ID | Status | Created | Date | Submit Dt | Updated | SetID | Line | Line Status | Type | Trans Date | Amount | Currency | Tran Amt | Currency | User Rate | Multiplier | Divisor | Rate Type | Location | Merchant | Long Descr | No Rcpt | Name |
| 1566258 | 123456 | PD | 29/01/2014 | 08/07/2015 | 19/12/2014 | 15/07/2015 | COMPANY | 2 | PD | CLNTENT | 21/11/2013 | 84 | EUR | 84 | EUR | S | 1 | 1 | DAY | DUBLN | Food Place | Client | N | Person 1 |
| 1566258 | 123456 | PD | 29/01/2014 | 08/07/2015 | 19/12/2014 | 15/07/2015 | COMPANY | 2 | PD | CLNTENT | 21/11/2013 | 84 | EUR | 84 | EUR | S | 1 | 1 | DAY | DUBLN | Food Place | Client | N | Person 2 |
| 1566258 | 123456 | PD | 29/01/2014 | 08/07/2015 | 19/12/2014 | 15/07/2015 | COMPANY | 2 | PD | CLNTENT | 21/11/2013 | 84 | EUR | 84 | EUR | S | 1 | 1 | DAY | DUBLN | Food Place | Client | N | Person 3 |
| 1566258 | 123456 | PD | 29/01/2014 | 08/07/2015 | 19/12/2014 | 15/07/2015 | COMPANY | 2 | PD | CLNTENT | 21/11/2013 | 84 | EUR | 84 | EUR | S | 1 | 1 | DAY | DUBLN | Food Place | Client | N | Person 4 |
| 1566258 | 123456 | PD | 29/01/2014 | 08/07/2015 | 19/12/2014 | 15/07/2015 | COMPANY | 2 | PD | CLNTENT | 21/11/2013 | 84 | EUR | 84 | EUR | S | 1 | 1 | DAY | DUBLN | Food Place | Client | N | Person 5 |
Report ID | EMPLOYEE_ID | Status | Created | Date | Submit Dt | Updated | SetID | Line | Line Status | Type | Trans Date | Amount | Currency | Tran Amt | Currency | User Rate | Multiplier | Divisor | Rate Type | Location | Merchant | Long Descr | No Rcpt | Name |
| 1566258 | 123456 | PD | 29/01/2014 | 08/07/2015 | 19/12/2014 | 15/07/2015 | COMPANY | 2 | PD | CLNTENT | 21/11/2013 | 84 | EUR | 84 | EUR | S | 1 | 1 | DAY | DUBLN | Food Place | Client | N | Person 1 |
| 1566258 | 123456 | PD | 29/01/2014 | 08/07/2015 | 19/12/2014 | 15/07/2015 | COMPANY | 2 | PD | CLNTENT | 21/11/2013 | 0 | EUR | 0 | EUR | S | 1 | 1 | DAY | DUBLN | Food Place | Client | N | Person 2 |
| 1566258 | 123456 | PD | 29/01/2014 | 08/07/2015 | 19/12/2014 | 15/07/2015 | COMPANY | 2 | PD | CLNTENT | 21/11/2013 | 0 | EUR | 0 | EUR | S | 1 | 1 | DAY | DUBLN | Food Place | Client | N | Person 3 |
| 1566258 | 123456 | PD | 29/01/2014 | 08/07/2015 | 19/12/2014 | 15/07/2015 | COMPANY | 2 | PD | CLNTENT | 21/11/2013 | 0 | EUR | 0 | EUR | S | 1 | 1 | DAY | DUBLN | Food Place | Client | N | Person 4 |
| 1566258 | 123456 | PD | 29/01/2014 | 08/07/2015 | 19/12/2014 | 15/07/2015 | COMPANY | 2 | PD | CLNTENT | 21/11/2013 | 0 | EUR | 0 | EUR | S | 1 | 1 | DAY | DUBLN | Food Place | Client | N | Person 5 |
Above is initial file 5 (rows) and below that is the same recordset and how I would like it to appear.
Report ID "1566258" is unique
Line "2" is unique to the duplicated line items for that claim only.
The last column contains the differing data which needs be retained.
I am not averse to doing a count of a concatenated Report ID and Line and dividing Trans Amount by Count ? That would also be acceptable to have 16.8 on 5 rows but the preferable outcome is one row with 84.
I'd appreciate some guidance on how to complete.
Obviously in a perfect world I'd have decent data to start with but I can't fix that so on to plan B.
Thanks in advance,
Rob
