Shiritori : A Complex Sorting Challenge
Background
Often in real life one may face complex sorting challenges which are beyond the scope of a sort tool. This example demonstrates how an iterative macro comes to rescue while solving a complex sort problem.
You can read about more about an Iterative macro and how to build it here:
Build Your First Iterative Macro!
The Shiritori Challenge
Shiritori is a popular word game where players are required to say a word which begins with the final sound of the previous word. Participants get a chance to say their word of choice in order to continue the word chain. Repetition of a word is not allowed and the game is terminated when a player chooses a word ending in ‘N’. For example: Tokyo -> Kyoto -> Tomioka -> Kainan (terminated)
In such a game, the sequence of words is hard to track and sorting the dataset based on the chronology poses a complex sorting challenge. The attached dataset comprises of city names which were spelled out in a Shiritori Game. Dataset was compiled from a wikipedia page. The challenge is to work your way out by sorting the list in a sequential order and find the first word.
Solution
This first thing is to find where the game got terminated. As per the rules of this game, the game will stop if a word ending in ‘N’ was used. So using a formula tool we can search through the dataset and realize that the game ended in “Tallinn” as shown below:
To sort the data, we must find its previous entry, followed by repeating this task to find all the previous entries until we reach the starting point. In order to achieve this we need an iterative macro.
To create a macro start with the input file and convert the input data to a macro input. Then, label the end point row and append that row to the entire dataset as shown here.
Then, make a row level comparison to find the entry where “Muscat” was the used previously.
To sort the remaining rows, we must create a placeholder which identifies the unprocessed rows of this iteration. Column “Min_sort” is created to identify such unprocessed rows.
In the last step of this macro, we would need a filter to identify sorted rows from unsorted rows. Unsorted rows will leads to an iterative loop anchor which will run until all the rows are sorted as required. The filter will then force the data to exit the loop and finally the results are passed to the output anchor as sorted data.
Open the workflow settings and change the macro as “iterative macro”. Open the “Interface designer” setting and choose the “Iteration input”, “Iteration output” and “Maximum number of iterations” as shown below:
Now create a workflow and feed the input data to this iterative macro, the results will be delivered in the Output anchor as shown below.
Conclusion
If you have a complex sorting problem which can’t be solved by sorting tool, as depicted here, then try to solve it using an iterative macro. If you are subscribed with our company as a registered user, then you can download the attached workflow and dataset from a specified webportal.
Alteryxの導入なら、クラスメソッドにおまかせください
日本初のAlteryxビジネスパートナーであるクラスメソッドが、Alteryxの導入から活用方法までサポートします。14日間の無料トライアルも実施中ですので、お気軽にご相談ください。
