Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

duplicates

ntudev
8 - Asteroid

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.

8 REPLIES 8
DataNath
17 - Castor

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:

 

DataNath_0-1657814593869.png

 

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

 

DataNath_4-1657820217697.png

binuacs
20 - Arcturus

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

binuacs_0-1657833461271.png

 

ntudev
8 - Asteroid

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

DataNath
17 - Castor

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:

 

DataNath_0-1658245650917.png

ntudev
8 - Asteroid

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
17 - Castor

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:

 

DataNath_0-1658255477896.png

ntudev
8 - Asteroid

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
8 - Asteroid

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

 

AsOfDateS&P RatingPrior Month S&PMoody RatingPrior Month MoodyMovement
5/31/2022901010.0003Moved down
5/31/202280700.38350.1753Moved Down
5/31/202254800.00740.3835Moved Up
5/31/202257800.01410.3835Moved Up
5/31/202270600.17531moved Up
Labels