Excel if-match formula

Question

Hi all,

I am still fairly new to Alteryx; not sure how to do an excel type of formula where i have two data sets.  One data set has a chain of command, and the other data set has the position of the various people.  I am trying to take the chain of command and basically put each person in their respective position, thus creating a hierarchy that has skip levels when an individual reports to someone a few levels above them.  For example:

Table 1:

EmployeeSupervisor 1Supervisor 2Supervisor 3Supervisor 4AHIJZBEJZ CZ   DQZ

Table 2:

DirectorRegional DirVice PresidentPresidentB    E    J    Z  Q H    I

Desired Output:

EmployeeDirectorRegional DirVice PresidentPresidentAHIJZB EJZC   ZD  QZ

In the past I have used an excel if-match type of formula.  I can't wrap my mind around doing this in Alteryx.

Thank you in advance for your help!

-rich

ddiesel · Accepted Answer

Hi @RTD !

I think the trick here is going to be to Transpose your tables so that the data is tall instead of wide. Then you can do your Join (aka match) followed by a Cross Tab to make your data wide again.

Let us know if this works for you!