Hi Friends,
I want to Crosstab every 10 records based on column names.. pls help me with the workflow to achieve the solution.
Solved! Go to Solution.
Hi Friends,
I ll give an example of my problem. i want to cross tab te below table using the column name.. pls help me on how to do this.
Name | Hike |
Address | Gravette |
Telephone | 444555666 |
Name | Mike |
Address | Granite |
Telephone | 22288877 |
Name | Hancock |
Hi aysha6029,
Great data blending question! I assume you want your data to end up like this:
Name | Address | Telephone |
Hike | Gravette | 444555666 |
Mike | Granite | 22288877 |
If so, there's a simple solution! All you need to do is add a Multi-Row formula tool before the Crosstab tool. I attached a workflow that demonstrates the answer.
The Multi-Row Formula is used to create a new field called Counter. Each time "Name" appears, the counter goes up by 1. So your table data will become:
Field1 | Field2 | Counter |
Name | Hike | 1 |
Address | Gravette | 1 |
Telephone | 444555666 | 1 |
Name | Mike | 2 |
Address | Granite | 2 |
Telephone | 22288877 | 2 |
Name | Hancock | 3 |
Here's the Multi-Row Formula's configuration:
You can now Crosstab this data grouping on the Counter. Here's the configuration
Tada! Let me know if you have any questions! Please mark if this solves your question!
Thank you,
DultonM
Thank You. Its working
Hi Friends,
I ran the Workflow and generated grouping numbers to transpose the records in proper order. My requirement is to neglected all the Result with Error ( Grouping 2) and take only the Success Records ( Grouping no 1 and 3) and transpose them to a excel sheet. Please help me to solve this problem.
Result | Success | Grouping |
Name | Mike | 1 |
Address | Gravette | 1 |
Result | Error | 2 |
Error Detail | Error out | 2 |
Result | Success | 3 |
Name | Harrison | 3 |
Address | Joplin | 3 |
Hi aysha6029! Will The Success field contain the word "Error" whenever that row failed? If so, one way to accomplish this is to....
1) add a Summarize tool. Group by on [Grouping] and Concatone [Success]. This collapses Success column down to 1 row per grouping.
2) Then use a Filter tool with the Contains function to identify all groupings that contain the word "Error".
3) Lastly, use a Join tool to join the original data (before the Summarize) with the false output from the Filter on the field "Grouping".
What comes out of the J output of the join will be only the groupings that didn't have "Error" on any of the rows! You can then transpose and output from there.
Hope this helps!
Hi DultonM,
Thanks for the explanation, I am encountering similar problems and I found this very helpful.
However, my problem is more troublesome. If I can refer to aysha6029's example, my table would look like below:
Field1 | Field2 | Field3 | Field4 |
Name | Hike | Peter | Lance |
Address | Gravette | London | DC |
Telephone | 444555666 | 1211313 | 24242 |
Name | Alaric | ||
Address | ANZ | ||
Telephone | 989876778 | ||
Name | Mike | Ronald | |
Address | Granite | Indonesia | |
Telephone | 22288877 | 798992 |
As you can see, each section has different number of columns. I am wondering if there is a way to transpose this table into the ideal form, which would be
Name | Address | Telephone |
Hike | Gravette | 444555666 |
Peter | London | 1211313 |
Lance | DC | 24242 |
Alaric | ANZ | 989876778 |
Mike | Granite | 22288877 |
Ronald | Indonesia | 798992 |
Thank you,
PANG
Hi Adams,
Thanks for the suggestion. I have raised a new thread for the same topic.
But as I am pretty much a rookie, do you mind to elaborate on your method to help me understand better?
Thanks,
PANG