We have a transactional system that I am trying to access and pull over some dates. I would like to populate fields with the date that a specific transaction code occurred. The challenge is some tranactions happen more than once and I think it would be helpful to capture the date of each of those transactions. This is an example of the source data:
| Cust_ID | Trans_NM | TRANS_ENT_CD | ENT_DT |
| A | 1 | CMET | 7/1/2016 |
| A | 2 | CMFA | 7/3/2016 |
| A | 3 | CMPB | 7/3/2016 |
| A | 4 | CMRS | 7/23/2016 |
| A | 5 | CMRL | 7/24/2020 |
| A | 6 | CMRL | 7/25/2023 |
| A | 7 | CMRL | 7/26/2028 |
| B | 1 | CMET | 3/25/2016 |
| B | 2 | CMNF | 5/26/2016 |
| B | 3 | CMNC | 5/27/2016 |
| B | 4 | CMNF | 6/15/2016 |
| B | 5 | CMNF | 6/25/2016 |
| B | 6 | CMNC | 6/26/2016 |
| B | 7 | CMAB | 7/12/2016 |
And I would like the output to look like this:
| CUST_ID | CMET_DT | CMNF1_DT | CMNF2_DT | CMNF3_DT | CMFA_DT | CMAB_DT | CMPB_DT | CMRS_DT | CMRL1_DT | CMRL2_DT | CMRL3_DT |
| A | 7/1/2016 | | | | 7/3/2016 | | 7/3/2016 | 7/23/2016 | 7/24/2020 | 7/25/2023 | 7/26/2028 |
| B | 3/25/2016 | 5/26/2016 | 6/15/2016 | 6/25/2016 | | 7/12/2016 | | | | | |
I seem to have backed myself into a corner where I can populate a field with the date but I have a record for each of the original transaction records. How do I get just one record for each customer?
In case it matters the source I am working with has millions of records.
Thank you.
