Joins

Hi,

I have 2058 records in 1st table and 93868 records in 2nd table. I am looking to capture only 2058 records required columns from 2nd table. Below is the example

When i try to captures the results by using join tool i haven't received desired results, please advise.

Thanks

Table 1.xlsx

Result.xlsx

Table 2.xlsx

Bug

Accepted answers

Luke_C

@Inactive User PMT is only sourced from table 2 though, so I'm not sure I follow. My latest update seems to align with the desired results you posted.

Without understanding the logic you need it's tough to say what needs to be done.

All comments

Luke_C

Hi @Inactive User

I had to make some assumptions here, but I got the result to have the 2,058 records. I joined on Empl ID, Code, and Year. Your issue lies in that in your table 2, a given employee ID, Code, Year combo can have different dates and payment amounts. To get them to line up 1 to 1, I used the summarize tool to do the following:

Min of First Date
Max of Last Date
Sum of Payments

New Workflow5.yxmd

unknown

Hello Luke,

It's close but PMT isn't payments hence it should be same as table 2.

Thanks

Luke_C

Hi @Inactive User,

You'll need to provide some additional requirements then. Either way the root cause is the same, you should be able to play around with the summarize/join tool to get the results you want.

Luke_C

@Inactive User I think changing the SUM to MAX for PMT gets you where you need to be

New Workflow5.yxmd

unknown

I have tried in different ways but couldn't get PMT same as table 2. PMT is related to the future estimation of target and it will be added to each employee's.

Thanks

Luke_C

@Inactive User PMT is only sourced from table 2 though, so I'm not sure I follow. My latest update seems to align with the desired results you posted.

Without understanding the logic you need it's tough to say what needs to be done.

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703