How to turn Data Value into Field Name (in Formula Tool?)

[bccpwc]

I've searched high and low and haven't found a solution for this hopefully I can explain this. 

I have a data set that looks something like this. The Group By column actually gets set earlier in my workflow and it can say either Department, Market or Region. and it will have it for either of the three for all rows. 

Emp ID	Department	Market	Region	Group By
1	HR	East	Global	Department
2	IT	East	Global	Department
3	HR	Central	Global	Department
4	Legal	Central	Global	Department
5	Ops	West	US	Department
6	Ops	Central	US	Department

In this case I want Group By to give me the actual values so that instead of the string "Department" it gives me the actual department for each Employee. 

Emp ID	Department	Market	Region	Group By
1	HR	East	Global	HR
2	IT	East	Global	IT
3	HR	Central	Global	HR
4	Legal	Central	Global	Legal
5	Ops	West	US	Ops
6	Ops	Central	US	Ops

This is a smaller piece of a bigger workflow but hopefully that makes sense. Thanks for the help in advance.

[bbak]

Could you use a formula tool that has "Group By" as your Output Column and says:

IIF([Group By]="Department", [Department],[Group By])

This would swap out the actual department name for every instance where the Group By column equals "Department"

You could do the same for Market and Region

[BenMoss · Accepted solution]

The complexity here is making it dynamic. 

In my instance I isolate the value in the group by field before performing a transpose against the rest of the dataset.

By performing a join against the name field and the value in my group by column I can then know the value that needs to be transferred into the 'Group By' column.

Solution attached, and it's dynamic so if 'Department' changes to Market it will cater for this.

 

[tyler_sassara]

You can use the formula tool to make your "Group By" field equal to the "Department" Field. Formula below.

Formula:

[Group by] = [Department]

[bbak]

@BenMoss

In the original question it only specifies that they wanted to pull the department values. I provided the other two formulas as examples in case there were other instances where values needed pulled over, but the dynamic setup is only needed if this dataset will have different "Group By" values over time and if every instance will require the new values to be pulled over. 

Great solution if this is the case, it just depends on whether the added complexity is necessary. 

[BenMoss]

@bbak my understanding was based on this statement.

'Group By column actually gets set earlier in my workflow and it can say either Department, Market or Region. and it will have it for either of the three for all rows. '

As you say I may be wrong, but that was just my interpretation :)

[bccpwc]

@BenMoss thank you for the solution, this is in fact what I needed!

Your understanding was correct in that it does need to be dynamic, thanks again!