How to convert header into row values and split header also into row values

Anuj_saxena01

Hi Experts , I am new to alteryx. so seeking for a quick help for a scenario. Thanks in advance. Here is my scenario-

I need to convert Table 1 into table 2. Here column like Jan(Fore),Jan(Current) etc. needs to be converted into rows (and as a new column i.e. Month and Time). Is there a way to do so in alteryx. Please help, Its urgent.

Table 1

Group	Jan (Fore)	Jan (Current)	Feb (Fore)	Feb (Current)	Mar (Fore)	Mar (Current)
ABC	$1,234	$2,123	$2,456	$2,457	$2,458	$2,459
ABCD	$1,235	$2,124	$2,456	$2,457	$2,458	$2,459
XYZ	$1,236	$2,125	$2,456	$2,457	$2,458	$2,459
XYZX	$1,237	$2,126	$2,456	$2,457	$2,458	$2,459

Table 2

Group	Month	Time	Sale
ABC	Jan	Current	$2,123
ABC	Feb	Current	$2,457
ABC	Mar	Current	$2,459
ABC	Jan	Fore	$1,234
ABC	Feb	Fore	$2,456
ABC	Mar	Fore	$2,458
ABCD	Jan	Current	$2,124
ABCD	Feb	Current	$2,457
ABCD	Mar	Current	$2,459
ABCD	Jan	Fore	$1,235
ABCD	Feb	Fore	$2,456
ABCD	Mar	Fore	$2,458

atcodedog05

Hi @Anuj_saxena01 

This is how you can do it.

Workflow:

1. Use transpose tool with Group as key to convert columns to row.

2. Use Regex parse mode to split Jan (Fore) into different columns.

3. Select tool to rename.

Here is a resource on 

Transpose tool : https://community.alteryx.com/t5/Interactive-Lessons/Changing-Data-Layouts/ta-p/82876

Regex tool parse mode : https://community.alteryx.com/t5/Interactive-Lessons/Parsing-Data-with-RegEx/ta-p/441415

Hope this helps 🙂

transpose.yxmd