Joining then Union, shift values to different columns

[wonka1234]

Hi,

I have a macro and then use a crosstab to join the results together:

I feel like my results in the 2nd row should be shifted over 3 columns.

Does anyone know how to do this?

[TheOC]

hey @wonka1234 
Try using the different configuration settings on the union tool:

These will allow you to specify how to join the data.

Hope this helps,
TheOC

[wonka1234]

this is so confusing, here are my two tables.

Person	Divison	Role
Louis	Actor	Lead
Tom	Actor	Support
Alan	Non-Actor	Support
Steve	Actor	Support
Person	Divison	Role
Louis	Actor	Lead
Tom	Actor	Support
Alan	Non-Actor	Lead
Steve	Actor	Support

Result is 

Person	Divison	Role	Right_Person	Right_Divison	Right_Role	Result
Alan	Non-Actor	Lead				Data 2 Unmatched in Data 1
Alan	Non-Actor	Support				Data 1 Unmatched in Data 2
Louis	Actor	Lead	Louis	Actor	Lead	Matched
Steve	Actor	Support	Steve	Actor	Support	Matched
Tom	Actor	Support	Tom	Actor	Support	Matched

but should be:

Person	Divison	Role	Right_Person	Right_Divison	Right_Role	Result
			Alan	Non-Actor	Lead	Data 2 Unmatched in Data 1
Alan	Non-Actor	Support				Data 1 Unmatched in Data 2
Louis	Actor	Lead	Louis	Actor	Lead	Matched
Steve	Actor	Support	Steve	Actor	Support	Matched
Tom	Actor	Support	Tom	Actor	Support	Matched

[clmc9601]

Hi @wonka1234,

Try adding a Select or a Dynamic Rename tool after your Right output anchor. Change Person, Division, and Role to be Right_Person, Right_Division, and Right_Role. By default, the Join happening inside your macro will not add the Right_ suffix to unmatched data from the right table.

[wonka1234]

@clmc9601 The select tool and renaming column works good, however what if my columes are not Person, Division and Role?

[clmc9601]

@wonka1234then use a Dynamic Rename (on the Developer tab) to "Add a Prefix/Suffix" to all fields. Add "Right_" as a Prefix and be sure to select "Dynamic and Unknown" fields. That will modify the names of every field that passes through the tool, even when they haven't shown up in the data yet.

[allwynthomas24]

Hey @wonka1234,

Please check the attached workflow and confirm whether it matches your listed requirement.

Thanks & Regards,

Allwyn.