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 |
Solved! Go to 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 : )
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