Recreate Vlookup Using Join Tool

[jmmart08]

Hi All I am trying to incorporate another input to my attached workflow and use the join tool to recreate the Vlookup on the vlookup formula sheet (example =VLOOKUP($V3,'P:\1.3_operations_critical\Collateral Management\Inventory\Weekly Inventory\[Inventory Categorization.xlsx]Sheet3'!$D:$L,6,FALSE) within my example sheet by using the lookup table sheet to find matches, build logic based on this, and also incorporate anything that isn't joined as I need all the data. Also, apologies but when I click browse nothing happens and I can't drag and drop files. Anyone know if there is an issue? I've never had an issue attaching files but I've tried multiple times today and I just cannot. I know what I have here is incomplete because I can't attach anything. Any guidance would be great appreciated!! Lookup Table Issuer ID Column J Issue Minor Column P Remaining Maturity Col V Unique ID JPM WFB UST000 U.S. Government 0 UST000U.S. Government0 99.00% 98.00% UST000 U.S. Government 0 UST000U.S. Government0 99.00% 98.00% UST000 U.S. Government 1 UST000U.S. Government1 97.00% 98.00% UST000 U.S. Government 2 UST000U.S. Government2 97.00% 98.00% UST000 U.S. Government 3 UST000U.S. Government3 97.00% 98.00% UST000 U.S. Government 4 UST000U.S. Government4 97.00% 98.00% UST000 U.S. Government 5 UST000U.S. Government5 96.00% 96.00% UST000 U.S. Government 6 UST000U.S. Government6 96.00% 96.00% UST000 U.S. Government 7 UST000U.S. Government7 96.00% 96.00% UST000 U.S. Government 8 UST000U.S. Government8 96.00% 96.00% UST000 U.S. Government 9 UST000U.S. Government9 96.00% 96.00% UST000 U.S. Government 10 UST000U.S. Government10 91.00% 96.00% UST000 U.S. Government 11 UST000U.S. Government11 91.00% 96.00% UST000 U.S. Government 12 UST000U.S. Government12 91.00% 96.00% UST000 U.S. Government 13 UST000U.S. Government13 91.00% 96.00% UST000 U.S. Government 14 UST000U.S. Government14 91.00% 96.00% UST000 U.S. Government 15 UST000U.S. Government15 91.00% 96.00% UST000 U.S. Government 16 UST000U.S. Government16 91.00% 96.00% UST000 U.S. Government 17 UST000U.S. Government17 91.00% 96.00% UST000 U.S. Government 18 UST000U.S. Government18 91.00% 96.00% UST000 U.S. Government 19 UST000U.S. Government19 91.00% 96.00% UST000 U.S. Government 20 UST000U.S. Government20 91.00% 96.00% UST000 U.S. Government 21 UST000U.S. Government21 91.00% 96.00% UST000 U.S. Government 22 UST000U.S. Government22 91.00% 96.00% UST000 U.S. Government 23 UST000U.S. Government23 91.00% 96.00% UST000 U.S. Government 24 UST000U.S. Government24 91.00% 96.00% UST000 U.S. Government 25 UST000U.S. Government25 91.00% 96.00% UST000 U.S. Government 26 UST000U.S. Government26 91.00% 96.00% UST000 U.S. Government 27 UST000U.S. Government27 91.00% 96.00% UST000 U.S. Government 28 UST000U.S. Government28 91.00% 96.00% UST000 U.S. Government 29 UST000U.S. Government29 91.00% 96.00% UST000 U.S. Government 30 UST000U.S. Government30 91.00% 96.00% FHLMC0 U.S. Government NA FHLMC0U.S. GovernmentNA 91.00% 92.00% FNMA00 U.S. Government NA FNMA00U.S. GovernmentNA 91.00% 92.00% GNMA00 U.S. Government NA GNMA00U.S. GovernmentNA 91.00% 92.00% GNMAII U.S. Government NA GNMAIIU.S. GovernmentNA 91.00% 92.00% 628962 U.S. Government NA 628962U.S. GovernmentNA Not Eligible Not Eligible GSE999 ABS NA GSE999ABSNA Not Eligible Not Eligible GSE999 CMO NA GSE999CMONA Not Eligible Not Eligible GSE999 Commercial Paper NA GSE999Commercial PaperNA Not Eligible Not Eligible GSE999 Corporate Bonds NA GSE999Corporate BondsNA Not Eligible Not Eligible GSE999 Lotteries NA GSE999LotteriesNA Not Eligible Not Eligible GSE999 Munis NA GSE999MunisNA Not Eligible Not Eligible GSE999 Repo NA GSE999RepoNA Not Eligible Not Eligible

[jrgo]

@jmmart08 

As it relates to using Union's or Join's, Union adds rows, Joins add columns (in most cases).

So I think all it's missing is just another Union tool after that last Join tool in the workflow I shared last. Join adds in your new columns from the other input file and the Union brings back in all the other records where a match wasn't found.

Here's the result which I sorted on the "Security_ID" field to confirm that it's only showing up once and looking at the right side, you see the additional fields that were joined in from that additional input. All unjoined records from the left are retained

Lastly, being new to Alteryx, but looking like you're a proficient Excel user, I'd strongly encourage you to run through the interactive lesson linked below. Shows how certain/common features in Excel translates to Alteryx.

Alteryx for Excel users 

There's also a ton of other on demand videos you can look through. tip, use the "Labels" on the right side to filter down to a specific keyword (click the next to show additional).