Joins

[Inactive User]

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

[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:

1. Min of First Date
2. Max of Last Date
3. Sum of Payments

[Inactive User]

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

[Inactive User]

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 · Accepted solution]

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