Need help for Formula

[Pinky]

Hi,

I have data like shown in below.

Input
ID	Name
111	America
111	America
111	America
111	West Virginia
111	West Virginia
222	South America
222	Portland
222	Florida
222	Florida
333	Newyork
333	Newyork
333	California
333	Nevada
333	Nevada
444	Chicago
444	Chicago
444	Chicago
444	Ohio
444	Ohio

I am expecting my output like below.

From and to: From where it is starting and where it is going. 

Final destinations: This is always last name for each id.

Output
ID	From	To	Final Destination
111	America	West Virginia	West Virginia
111
111
111
111
222	South America	Portland	Florida
222	Portland	Florida
222
222
333	Newyork	California	Nevada
333	California	Nevada
333
333
333
444	Chicago	Ohio	Ohio
444
444
444
444

[rafalolbert]

Hi @Pinky,

Nice puzzle by the way! 🙂

This is what i have to share with you, methodology is as follows:

- only take forward unique records across both columns

- count how many records there are for each id and create 2 sub-workflows

- get record at id group and lookup corresponding values as: from, to , destination (this is different between 2 sub-workflows)

- Cross-Tab data and Union

- point of difference for the 2nd sub-workflow is that for each id we append a new record as (from, to), which is derived from (to, final_destination) 

Thanks,

Rafal

#Excuse me, do you speak Alteryx?

[PhilipMannering · Accepted solution]

Hi Pinky,

Looks like you might have taken a wrong turn there @rafalolbert 

Here's another solution.

Workflow

Thanks,

Brain (Philip)