Blending data without loosing unmatched data set!

[Ranodip]

Hello Everyone,

Hoping this to be a simple fix-

I've two excel sheet. In one excel sheet I've created a Blank Column using the Filter tool and is blank (" "  here's the expression I've used to keep it blank).

Now, I want to pull data from another sheet like a look up with One column as a common reference which is common in both the sheet.

I don't want to loose out on the data for which the reference column values doesn't match. Like in excel for vLook up, it gives #N/A as a value. But here in Alteryx it removes the unmatched values.

Please Help!!

[MarqueeCrew · Accepted solution]

@Ranodip,

I may not understand the post, but here is a way to perform a vLookup and keep the unmatched data.

1. Join Sheet 1 (Left) and Sheet 2 (Right) on a common key (Lookup).
2. Take the J output into a UNION tool configured as auto by name and to keep all fields and to ignore warnings when fields do not match.  I'd also remove the "duplicate" fields by unchecking them from the Right input.
3. Optionally add a formula tool to the left output anchor where the formula is:  "#N/A".  This is entirely unnecessary but it makes the output look like excel.  In Alteryx, the value for the added fields is Null() and is easily found.
4. Now add the L output data to the Union tool.

This is covered in the interactive training for Excel users.  You might want to check it out.

Cheers,

Mark

[NickC · Accepted solution]

Hello,

It sounds like you need to use a join on the common field.  This will join up all the fields that have a common reference, in order to include those blank columns you will need to use a union tool afterwards and connect either the L and the J or the R and the J.  

This is known as left outer join or a right outer join.  

https://www.alteryx.com/alteryx-for-sql

Let me know if that doesn't help.

Thanks,

Nick

[Ranodip]

Thank you Nick, it worked like magic. :)