AnsweredAssumed Answered

Summarizing and Merging Data based on Partial matching on a field?

Question asked by AaronTokunaga on Jul 30, 2013
Latest reply on Jul 31, 2013 by AaronTokunaga


Summarizing and Merging Data based on Partial matching on a field?


     I apologize in advance if this was asked already but I searched and could not find a similar situation.

     Currently, the management is requesting a remake of the now ancient financial system originally written using a dead piece of software "The Card 8.0" by iFour (Japanese company).

     The system has been modernized and most features are flawlessly working and only required the office staff stop trying to make special cases for everything.

     However, we have one feature that must be implemented to follow company policy/procedure and I'll try to describe it as simply as I can:


     * Each customer has an account for various classes/courses (This is a music, rhythmic and language school) and they have their own Account ID

     * Customers may have multiple accounts in cases such as they have mutiple children enrolled in different courses or they are a teacher in a separate school enrolling a class, etc.

     * Customer IDs are multiples of 10 in general (e.g. 1010, 1020, etc.) but in the event of multiple accounts per family/group the IDs increment by 1 starting with a 1 in the last digit.  (e.g. 1011, 1012, 1013)

     * In the montly data we output in CSV format to give to the bank for their proprietary system, we do not wish to charge the same customer twice, so using the previous example, all the other records output for accounts 1011,1012,1013 would just use the data from 1011, and the total monetary amount in the last field would be summed over the three records and output at the end. The staff has had bad habits in the past though and the name, address and bank account information fields in the case of multiple accounts is not always present beyond the first record (which doesn't matter since it should only use the data in the first record)



     ID         (Various name, address and bank account fields)      Total Amount

     1011             (stuff)                                                                              1000

     1012             (stuff)                                                                              1500

          1013             (stuff)                                                                              2000


     Should be output as


     ID         (Various name, address and bank account fields)      Total Amount

     1011             (stuff)                                                                              4500

     I currently have a silly python script that runs on the data to do just this but it is kind of a kludgy hack and I would like a more elegant solution.