Cross Tab to convert multiple rows into single rows with additional columns

[srikant]

For this input 

Name	Tele	Txn	Date
John	510	ABC	8/5/2017
John	510	DEF	8/12/2017

I need this output 

Name	Tele	Txn	Date	TXN_1	Date_1
John	510	ABC	8/5/2017	DEF	8/12/2017

[JohnJPS · Accepted solution]

Hi @srikant

One thing you could try is to concatenate the fields using the Summary tool, then split them using the Text to Columns tool; (see attached).

Caveats

1. You'll need as many Text-to-Column tools as distinct columns of interest (e.g. in this case two: TXN and Date)

2. Your text-to-columns tools will need to allow for as many rows as are possible in your dataset; (e.g. in this case two: ABC and DEF) 

Hope that helps!

[NicoleJohnson · Accepted solution]

Try the attached method... by assigning a RecordID at the beginning, you can use that to create your "Txn_1", "Txn_2" etc. columns.

1. Add RecordID tool

2. Transpose to get Txn & Date column headers into one column

3. Use Formula tool to combine the original headers with RecordID field. (NOTE: If you want them to appear in record order, you'll want to put the RecordID before the header like RecordID+"_"+Name2, as when you Cross-Tab in the next step, it automatically puts your fields in alphanumeric order)

4. Use Crosstab tool (group by Name & Tele) and then use your new Header column for header and Values field for values

Hope that helps!

NJ

[srikant]

i have 11.03 and it says i have to be in the next version. i not this ALTERYX big goof up chasing versions ?

[srikant]

Thanks . JohnJPS you rock 

[srikant]

thank you so much