Recreate Vlookup Using Join Tool
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Solved! Go to Solution.
- Labels:
- Developer Tools
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @jrgo
I was able to use Chrome to attach my inputs and workflow. Any further guidance you might be able to give me with the actual inputs? Let me know if my original post makes sense.
Thank you very much.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Here you go... as illustrated in my last post, use a Union tool to keep all the UNJOINED (unmatched) data from one side with the joined/matched data (from the J output). in this case, your main data was connected to the L input of the JOIN and I connected both the L and J output of the Join into the Union.
On a side note, unlike a VLOOKUP which requires that you concatenate all the values to create your lookup value, with a JOIN tool, you don't need to do that and can just select each field to establish the join (or lookup) on.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @jrgo
This is GREAT!! Thank you very much. I had 1 final question. I have to incorporate 1 last input into my process. I have attached this. There is only 1 item on here but in the future there could be several items (not a set amount) that I need to add to my join and union. I have attached the file. I need to add 3 columns to the end of my output from this file (Amount already on deposit, reason for deposit, and available units. The amount already on deposit and reason for deposit columns are once again using a Vlookup in the excel sheets. It is looking for the Unique ID column on my original Example excel sheet and then referencing a vlookup on the file I have now attached and looking at columns to return. I'm not sure where to incorporate this into the join or union. If you can provide some guidance that would be amazing. I really appreciate your assistance up to this point. This is tremendous!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @jrgo
Sorry to reach out again. I am trying to join 1 more excel file file in my entire workflow and can't seem to figure it out. The attached may have data that is also on my original input example spreadsheet and I need those to figure in to my join/union. I can write the logic necessary to add some additional columns but when I try to join this what is happening is I'm duplicating the security ID and primary sec ID. I want them to mesh and have them part of my over all flow as 1 line item with the current logic I have built in. Any assistance/guidance to send me down the right path would be very much appreciated.
Thanks so much!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @jmmart08
I’ll take a look at this tomorrow (already away from PC). Can you share an example of what you’re expecting to see after the file you shared is joined in?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @jrgo
Thank you. Please see the attached workflow. I am just trying to incorporate 1 more input (Query on Deposit Inventory GNWY 03.21.21) into the workflow you already helped me with. I need to matchup any values in the PRIMARYSECID column on this sheet with the same SECURITY_ID on my example sheet but just add 3 more columns. What I can't figure out is how to join into 1 line item with all of the same logic in my original, plus the formulas I created for this new file. What I get now is 2 separate line items as seen below for SECURITY_ID 912810ET1 as this is the value that is on both sheets. Again I am just trying to combine this into 1 line item with all of my formulas applying to all columns plus adding the 3 additional columns (Amount already on deposit, reason for deposit, available units) to the end of my original workflow. Hopefully this makes sense and thank you so much for your help!
AS_OF_DATE | SAP | PORTFOLIO | SECURITY_ID | GL_GROUP_CODE | ISSUER_IDENTIFIER | ISSUER_NAME | ISSUER_MAJOR_GROUP | ISSUER_MINOR_GROUP | ISSUE_NAME | ISSUE_MAJOR_GROUP | ISSUE_MINOR_GROUP | COUPON_RATE_BASE | MATURITY_DATE_BASE | SumOfAS_OF_PAR_SHARES | SumOfAS_OF_MARKET_VALUE | Unique ID | Held as of Previous File? | Hedge Port? | Remaining Maturity | Maturity Truncated or NA | Unique Collateral ID | JPM | WFB | Issuer ID Column J | Issue Minor Column P | Remaining Maturity Col V | Right_Unique ID | Right_JPM | Right_WFB | F7 | PRIMARYSECID | AMOUNT | REASON | Amount already on deposit | Reason for Deposit | Available Units |
3/31/2021 | 1330 | 21099 | 912810ET1 | 20 | UST000 | UNITED STATES OF AMERICA | U.S. GOVERNMENTS | U.S. GOVERNMENTS | TREASURY BONDS | Fixed Income - Non MBS | U.S. Government | 1 | 2/15/2025 | 1000000 | 1250595.45 | 21099912810ET1 | 3.88 | 3 | UST000U.S. Government3 | UST000 | U.S. Government | 3 | UST000U.S. Government3 | 0.97 | 0.98 | |||||||||||
3/31/2021 | 1330 | 21099 | 912810ET1 | 20 | UST000 | UNITED STATES OF AMERICA | U.S. GOVERNMENTS | U.S. GOVERNMENTS | TREASURY BONDS | Fixed Income - Non MBS | U.S. Government | 1 | 2/15/2025 | 1000000 | 1250595.45 | 912810ET1 | 1500000 | All Policyholders | 1500000 | All Policyholders | -500000 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @jrgo
Is there a way to basically just collapse that security 912810ET1? I want it to be part of my overall output not just that security as isolated. Just can't figure out where to add in joins and/or unions or another tool to incorporate this to the overall output. In the last join on the workflow you provided, I still have this ID 912810ET1 as part of my left and joined outputs. Looking to see if I can incorporate all of the logic, additional columns, etc into 1 line item and still output all of my data. Let me know if that makes any sense. As the setup is now, my join just isolates the 1 security and it outputs what I expected for that 1 ID but that ID also still exists in my left output as well as the join output. Hopefully this helps if. Thanks again for taking the time to help.
