duplicates

Hello,

I'm trying to do something like a VLOOKUP but when I do a join it keeps duplicating, I just want the Moodys section in converted tab to be filled by according to the dictionary. I think its quite easy, i just cant figure out how to not duplicate. I attached the file.

test2.xlsx

Common Use Cases

Accepted answers

DataNath

How does this look @ntudev? I've just conducted one join at a time, as trying to conduct them both at once wouldn't work for records where the month/prior month are different. There's one instance (Prior Month S&P = 60) that doesn't match anything in the Dictionary table and so I've just unioned this record back on due to it getting dropped during the join (hence why the Prior Month Moody is null). If you want this to be dropped then obviously just remove the Union tool:

Join current and prior month moody rating.yxmd

DataNath

Like this @ntudev? I've only done 3 here hence why the others are just nulled because of the else null() part of my expression. If you wanted to do Prior month then you'd just do exactly the same. However, in cases like this it's definitely easier to use a join where you can instantly pull in the values based on a reference as writing out an if statement with so many different possibilities is time-consuming and not dynamic if more are added:

Current and Prior month moody by formula.yxmd

All comments

DataNath

What were you joining on @ntudev? Was it the Date by any chance? I've just tried conducting the join based on the S&P rating, and the values seem to line up fine. There's duplicates (2 of each) because you have 2 of each S&P rating in your 'Converted' tab:

Joining on Date (all dates are the same and so it'll join all Table 1 values to all Table 2 values giving 42*21 records):

Moody rating join.yxmd

binu_acs

@ntudev one way of doing this by joining the As of Date and S&P rating

join_14_07.yxmd

ntudev

If i was to add another tab for prior month Moody, how would i join it?

test2.xlsx

DataNath

Join current and prior month moody rating.yxmd

ntudev

this works great, i was also wondering how would i write a formula to do the conversion instead of a join? can you just pick 2 or 3 and write a formula?

DataNath

Current and Prior month moody by formula.yxmd

ntudev

these are perfect, i just had another smaller data that needed to be added and it was duplicating so this formula solves it. Thank you.

ntudev

how would i get it to add another column to say Moved up or Moved Down if the numbers are different?

AsOfDate	S&P Rating	Prior Month S&P	Moody Rating	Prior Month Moody	Movement
5/31/2022	90	10	1	0.0003	Moved down
5/31/2022	80	70	0.3835	0.1753	Moved Down
5/31/2022	54	80	0.0074	0.3835	Moved Up
5/31/2022	57	80	0.0141	0.3835	Moved Up
5/31/2022	70	60	0.1753	1	moved Up

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703