Combining Fields

[HM]

Hi,

I was to combine 2 fields into 1 and have all the other fields mapped to it as well as adding a column to identify the Type - example below:

Input:

Dependent	Enabler	Owner	Dependent ID	Enabler ID	Request ID
A	Z	Tim	1	13	100
B	Y	Harry	2	10	250
B	X	Hope	2	26	200
C	Z	John	3	13	400
D	W	Angela	4	24	570

Desired Output:

Request ID	ID	Project	Type	Owner	Relationship
100	1	A	Dependent	Tim	A dependent on Z
250	2	B	Dependent	Harry	B dependent on Y
200	2	B	Dependent	Hope	B dependent on X
400	3	C	Dependent	John	C dependent on Z
570	4	D	Dependent	Angela	D dependent on W
100	13	Z	Enabler	Tim	A dependent on Z
400	13	Z	Enabler	John	C dependent on Z
250	10	Y	Enabler	Harry	B dependent on Y
200	26	X	Enabler	Hope	B dependent on X
570	24	W	Enabler	Angela	D dependent on W

Thanks!

[s_pichaipillai]

HM,

try this, i did not clean up much (busy with my work :)) but it provides your output

well, there are so many ways but you can get the logic here :-)

thanks

Saravanan

[jdunkerley79 · Accepted solution]

Had a quick go.

Used a formula tool to create a relationship field and a couple of fixed values for the types.

You can then use a couple of select tools to create two sets one of Dependents and one of Enablers 

Finally use a union tool to join them back together. 

There are lots of ways to do this one but this way felt the simplest.

[HM]

Thank you so much - the solution is simple and effective! 

[HM]

Sorry - just one question about the solution: the formula for the relationship field seems to cut off some of the text when I use it on my real dataset, is there a character limit to this?

Thanks.

[jdunkerley79 · Accepted solution]

In the formula tool the length of the field is specified when creating the relationship. I expect left at the default when I created it (64).

You can just increase the number to give you enough characters there.