data manipulation

[d1miller99]

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?

[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

[binuacs]

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

[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

@binuacs  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

[ShankerV]

Hi @d1miller99 

Please share the desired output as it will be easy to understand.

Thanks in advance. It will be easy.

[d1miller99 · Accepted solution]

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