Iferror Vlookup help!!

[Metadata21]

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!

[Qiu]

@Metadata21 
Your Excel file would be really helpful if you could share.

[IraWatt]

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

[Metadata21]

[IraWatt]

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.

[Metadata21]

Hi - now attached 

[Metadata21]

now attached with excel file - basically i would need to create a new column with each of the formulas included within the cells 

[DawnDuong]

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:

1. 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
2. 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
3. ELSE set value equal to BW ENDIF

To implement it in Alteryx:

1. 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).
2. 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.
3. 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.
4. 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.

[Metadata21]

Hi Dawn can you show me how this would look in alteryx as i am so confused :-(

[DawnDuong · Accepted solution]

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.