Generate Missing Rows

[twentytu]

Hi Alteryx Pros,

I know there are many topics about generating missing rows, but my case is a little different. 

I need help convert Left table to Right table. 

As you can see here, each ID might have multiple TYPE and VALUE. Every time when there is a TYPE, there must be a VALUE following. Please help, thanks in advance. 

LEFT				RIGHT
ID	Category	RecordID		ID	Category	RecordID
101	TYPE	3		101	TYPE	3
101	TYPE	3		101	VALUE	4
101	VALUE	4		101	TYPE	3
102	TYPE	3		101	VALUE	4
102	TYPE	3		102	TYPE	3
102	VALUE	4		102	VALUE	4
103	TYPE	3		102	TYPE	3
103	TYPE	3		102	VALUE	4
103	VALUE	4		103	TYPE	3
103	TYPE	3		103	VALUE	4
103	VALUE	4		103	TYPE	3
104	TYPE	3		103	VALUE	4
104	TYPE	3		103	TYPE	3
104	VALUE	4		103	VALUE	4
				104	TYPE	3
				104	VALUE	4
				104	TYPE	3
				104	VALUE	4

[atcodedog05 · Accepted solution]

Hi @twentytu 

Here is how you can do it.

Workflow:

1. Using multirow formula tool to check whether Value row needs to be generated for Type and flag the Type row.

2. Using generate rows tool generating extra row for the flagged Type rows.

3. Using formula tool to replace the values in category and record ID for the newly generated Value row.

4. Using select tool to keep only the required records.

Hope this helps : )

[Elias_Nordlinder]

Hello,

I am not the OP but I just want to say that I really like the solution and how "Easy" it looks

to solve a problem like the one above with Alteryx and just the preparation tools.

Great use case of Generate Rows and Multi-Field Formula, I feel like I

utilize these tools too little and have to incorporate more in my flows at work.

They are great tools in use cases like this one 🙂

//Elias