Iferror Vlookup help!!
- 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
Hi, I am new to using Alteryx and have been stuck on this for hours, how would I transform this formula into an Alteryx workflow?
Please help me
Appreciate the support!
Solved! Go to Solution.
- Labels:
- Connectors
- Custom Tools
- Developer Tools
- Join
- Output
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Metadata21
Your Excel file would be really helpful if you could share.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @Metadata21,
Cant exactly see the formula in the screenshot but VLookups are essentially Joins in Alteryx. The only difference being Alteryx will join on every record where as Vlook ups join on the first record which matches only. I've attached the example join workflow. If you have any further questions make sure to ask.
HTH,
Ira
- 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
Hey @Metadata21,
Would you be able to share the excel file with the data (or dummy data) with the formula? I cant copy the data from a screenshot.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi - now attached
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
now attached with excel file - basically i would need to create a new column with each of the formulas included within the cells
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Metadata21
The excel formula can be replicated with a Join tool. I explain below the schematics so that you can try again in Alteryx. Basically you want to achieve the results of nested ifs.
First, let's analyze the Excel formula to see what the logic tree looks like:
- IF AQ matches any value in column DE THEN set value to be equal to the value of numerically formatted value in column DF of the matched row
- ELSEIF BW matches any value in column CZ THEN set value to be equal to the value of numerically formatted value in column DA of the matched row
- ELSE set value equal to BW ENDIF
To implement it in Alteryx:
- Join Tool with both L and R input anchors set to the same input tab, and set the join condition with L = column AQ and R = column DE. Remember to Deselect Duplicates.
- The J output anchor will give you the rows where the match is satisfy. Then you create a new column ("Alliant Vendor") with a Formula tool to set this to be equal to DF.
- The L output anchor will give you all the unmatched rows, which can be used for step 2 (a.k.a. intermediary).
- Add another join tool right after that, with both the L and R input anchors connected to the L output of the first Join tool (i.e. the intermediary). Then set the join condition with L = column BW and R = column CZ. Remember to Deselect Duplicates.
- The J output anchor will give you the rows where the match is satisfy. Use a Formula tool to set The "Alliant Vendor" column to be equal to DA.
- The L output anchor will give you all the unmatched rows, use a a Formula tool to set the "Alliant Vendor" column to be equal to BW.
- Use a Union tool to join the J and L output anchors of step 2. If the values in columns AQ and BW (i.e. your join conditions) are unique, then the number of records will remain unchanged.
- If you find that the number o records change, it is likely because the join conditions are not unique, in which case, you need to decide whether you only want to follow Excel's convention of taking on the first match (which can be done by addition the Unique Tool after each Join step > i'll leave you to explore this).
hope this helps.
Cheers,
Dawn.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Dawn can you show me how this would look in alteryx as i am so confused :-(
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Metadata21
I am not sure what your use case is. What I observe is that your data consists of (i) columns which are "value only" and (ii) columns which are dependent on other columns. So the values are not correct for all the rows because from column CA onwards, there are only 5 rows of records.
Without loss of generality, I attached here the results for the first 5 rows.
It is a good practice to separate "data" vs. "intermediate values". However, in this case, they are commingled, which makes it impossible to trace and also not possible to generate the full data set for all the rows without also updating the values in the other "dependent" columns.
Aside from just trying to "Solve", I think you will find it very helpful to check out the "Interactive Lessons" and get your skills honed with smaller sets of data first.
Cheers,
Dawn.
