Join Tool not having the correct output. How to add empty fields?

[Joker_Hazard]

Hello guys, how are you doing today?

I am trying to join two datasets together. 

First dataset is a dummy text input (which contains the name of 5 testers people).

Second dataset is exported directly thought SQL and contains the name of the testers of the day. Normally only three or two contribute on each date.

The problem is that when joining it is not showing the five testers (some with NULL values). Its only showing the testers of the day which is wrong, example:

When combining then, they are joined like this: 

The correct WOULD be like this:

How to solve this? Thanks 🙂

[apathetichell]

you are using the union tool which add records as opposed to the join tool which allows for an inner join or a join and then a union which allows for a left/right/inner/outer join.

see attached.

[Joker_Hazard]

Hey Apathetic

There is a problem, the input from SQL does not have a constant column. It varies, it could be like this:

Or like this:

Then the union tool does not work..

[apathetichell]

Off the top of my head the two solution  fast solutions I'd use are transpose  with your tester as a key column (and then then join on name as a second join) and  then crosstab back or.... The problem is you'll have to set up a multi-field to convert back after crosstab... again if you have semi-static total fields and you know the ones you want as numbers - very doable.

the second is to use the crew macro to to ensure fields for all of the fields in the right/sql input. Then you can just select only the fields from the right tool - and you don't need to worry about any fields being left out... ensure fields would go inbetween the right field and the join.

[Joker_Hazard]

I see. I will have to create another workaround then, maybe using excel. 

I cant use crew macros in my organization, its very restricted when the subject is third parties solutions. 

Lets see how it goes! Thanks 🙂

[apathetichell]

There are also some batch macro solutions and I might be able to come up with a dynamic replace - just want to articulate the problem - you want the data from the right tool but you want all of the fields from the left tool and all of the records from left tool as well - correct?

Try this version too - I believe that Join's are LEFT dominant meaning that in case of fieldname conflict those coming in from the right tool will get renamed "right_" because your right input is your dynamic input data you want to keep those fields and you want to drop any of the other fields. I swapped which tool comes in where and added a dynamic replace which should filter out any "right_" fields under the assumption that any duplicate fields which come in will be renamed as "right_fieldname"... so give it a try.

[Joker_Hazard]

Yep that's correct! If its too much of a problem, I understand that its okay to let it like this. I have a solution for this, the workaround would be to use excel and create a macro there, its a deviation from my original idea, but would work. 

Thanks once again for the help 🙂

[apathetichell]

check out the workflow I posted and let me know if that's closer and how it deals with your dynamic data.