Populating data elements based on transactional code into a single record

[Nosal25]

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.

[patrick_digan · Accepted solution]

@Nosal25 Can you give the attached a try? I'm making use of the crosstab tool to reorient the data. The key is using the multi-row tool grouping by Cust_ID and TRANS_ENT_CD to ensure that the first of each TRANS_ENT_CD lines up.

Let me know if that doesn't solve it!

[Nosal25]

Thank you.  That worked great for the sample data set and really helps me along.  When I ran the actual data through, I got a lot of errors.  Is it possible to reset that count with each customer ID?    

[patrick_digan]

@Nosal25 Can you share the exact error message? Since the count is grouping by customer ID, it seems like that should still work on a big data set...

[Nosal25]

"Dynamic Rename (49) Could not generate a unique name for field <ABN2_DT>, number 2594, after attempting 999 generated names"

???

[patrick_digan · Accepted solution]

@Nosal25 Interesting! In that case, I would 1) remove the dynamic rename tool altogether. 2)  Modify the formula tool (if needed) to get column headings that you are comfortable with/need. Currently, I have the formula: 

[TRANS_ENT_CD]+'_'+tostring([NewField])+'_'

I was originally trying to use the dynamic rename tool but you may very well be able to get by without it.