data manipulation

I have a table, for example the personnel table, that would have the following column: empno

There would be another table, salesinvoice, that would have the following columns:

I would then have a salesinvoice table that has the following columns: current_emp, alternate_emp

I need to match both the current_emp, alternate_emp from salesinvoice to the empno from personnel. A table join would not work because it only goes row by row and if current_emp, alternate_emp has separate values, then i would not get a match.

Is there a way to do a join that would not go row by row so that I do not have to do two separate joins to match each field?

example1.yxmd

Best Practices

Join

Accepted answers

d1miller99

I found a way, and i do not know why i did not think of this. so i still have to do two joins but i can do the two joins out of one input

All comments

PanPP

Hi @d1miller99

One option is to use a join tool, and then use a union tool to stack all records (all 3 anchors) from the output of a join tool.

Another option is to use a union tool to stack all the records vertically on top of each other, then try to remove duplicate values.

ShankerV

Hi @d1miller99

Can you please share the desired output.

Many thanks

Shanker V

binu_acs

@d1miller99 One way of doing this is by Transpose the second table and joining empno and value

example1.yxmd

d1miller99

thanks for the replies

@ShankerV What I am trying to have the 7 records in salesinvoice where the fields(current_emp and alternate_emp) exist in the personnel table. As you see the issue is the alternate_emp and the current_emp will not be the same in many cases for the different rows in salesinvoice

@binu_acs the transpose was actually creating more records. In reality, I need the dataset from the salesinvoice dataset to stay intact, i am just making sure that there is a match on the current_emp and alternate_emp

@PanPP I did think of a table join, but it did not work because it was trying to join two fields on a row by row basis and in many cases, current_emp and alternate_emp were not the same and thus there would not match with the employee. I guess it joins the first field in join configuration first?

Basically the end result is that i checked that current_emp and alternate_emp existed in the personnel table. I was hoping that one tool could accomplish this and that i had somehow missed something. Unfortunately, it looks like i will need to have two personnel inputs with two separate joins to match both fields in the salesinvoice

d1miller99

I found a way, and i do not know why i did not think of this. so i still have to do two joins but i can do the two joins out of one input

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703