community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Advanced Transposing

Asteroid

Hello,

today I received some Excel files where the standard transpose tool does not work for me.

The columns are like the following: Column name and example data

 

Topic | subtopic | Jul 2018 : percentage resolution | Jul 2018 : total resolution | Aug 2018 :  percentage resolution | Aug 2018 : total resolution |

Helpdesk | Tickets | 0.85 | 4636 | 0.91 | 3663 |

Helpdesk | FirstFix | 0.69 | 1990 | 0.65 | 1801 |

 

I need the following structure:

Topic | subtopic | Month Year | percentage resolution | total resolution |

Helpdesk | Tickets | Jul 2018 |  0.85 | 4636 |

Helpdesk | Tickets | Aug 2018  0.91 | 3663 |

Helpdesk | FirstFix | Jul 2018 | 0.69 | 1990 |

Helpdesk | FirstFix | Aug 2018 | 0.65 | 1801 |

 

Any ideas how to solve this?

Alteryx Partner

Hi,

 

I've attached a sample workflow here,

 

advanced_transpose.PNG

 

I achieved this by transposing as usual, with topic and Subtopic as keys, then using the TextToColumns to split on the ":", followed by a REGEX_Replace to clean up the columns. Afterwards, I used the CrossTab Tool, grouping by Topic, Subtopic and Name1 (which is the date in this case), with Name2 as the new column headers, and Value as the values. Name1 and Name2 are the first and second columns created by the TextToColumns Tool.

 

Let me know if you have any other questions about this workflow,

 

Cheers!

Asteroid

Thanks tcroberts,

 

perfect - exactly what I need. 

Labels