How to merge unique values from multiple fields into one field

[owenkosnen]

Hello Experts,

Just wondering if anyone is kind enough to provide some clues or hints on how to solve this simple yet tricky data manipulation. question.

I'd like to merge multiple fields as shown below into one column while keeping the unique values and its relationship intact. Would it be possible?

Occupation > Gender > Year Joined > Age

[Before]

  

[Intended Result]

Is there any quick way to do this? perhaps I am missing out some tools that can perform such task.

Any clue or tips would be greatly appreciated.

[Qiu]

@owenkosnen 
Hope this is what you need.

[Emil_Kos]

Hi @owenkosnen,

It took me some time to understand what is the goal of the exercise and I think @Qiu created elegant solution for you. I have a quick question as I am curious about it why do you need a data in this format? 

[Qiu]

@Emil_Kos 
Thank you so much for your comment😁

[owenkosnen]

Hi @Qiu , Thanks for your solution, really appreciate your effort to help me out with this!

Just a quick question - it seems that the Unique Tool is not quite the right tool to use as it is deleting the duplicates even though they are actually not duplicates. For example: Teacher has 2 different 'Year Joined' but contain same age. One of the age is deleted by Unique Tool. I am having this problem hence not I am looking for other solution.

[Unique Tool]       [Intended Result]

Teacher               Teacher

   2010                     2010

     40                          40

   2011                     2011

                                   40

@Emil_Kos  - The list will be used as an input source to generate Tree UI using the Tree Interface Tool. I already have a workflow to assign reference key, all I need is to solve this puzzle.. Kudos to @Qiu !

[Emil_Kos]

Hi @owenkosnen,

Thank you for letting me know.

Good luck with your project!

[Qiu]

@owenkosnen 
I think you are right, I need to think something else later.

[Qiu · Accepted solution]

@owenkosnen 
Promise me you will not change the schema of input data, and this should work.

[owenkosnen]

The solution looks great! Thanks again @Qiu .

I have marked your piece as the solution - one quick question though, may I know why you decided to split the 'Butcher' out and rejoin it at later stage? I cannot seem to understand how it would make difference.. Would you kindly enlighten me?

[Qiu]

@owenkosnen 

Thank you for the accept mark.

And with your permission, I would like to submit this as an idea of weekly challenge, what do you think?

The multiple row tool will not work properly if there is only one entry, which will be filtered anyway.

So I use the summarize tool to count by Occupation and if only then goes to seperate process.

Hope I made myself understood.