VLOOKUP with Designer

[LauraFP]

Hi All

I would like to share with the community one issue that I am experimating when I apply a VLOOKUPwith Allteryx.

First at all, let me introduce the necessary data that I am trying to cross:

TABLE 1: shows a list of materials (A, B & C) but the Source of Manufacture Code is not assined = #.

MATERIAL	Source Of Mnf Code
A	#
B	#
C	#

TABLEL 2: shows in which location codes are being manufactured each material, and I sorted this data based on the code which has more sales.

MATERIAL	Source of Mnf Code 2	Total Sales
A	4356	5.000.000
A	4121	200.000
B	1789	400.000
B	1406	2.000
C	4513	1.000.000
C	4512	5.000

When you sorted the find table date and apply a VLOOKUP with Excel, the formula return the first row found (previously sorted by Main Sales Criteria). So, the VLOOKUP results in Excel will be:

MATERIAL	Source of Mnf	Source of Mnf 2
A	#	4356
B	#	1789
C	#	4513

To make it with Alteryx Deigner I am using a Find & Replace Tool but the results are not being as I expected based on Excel exeriences. Aalteryx is returning the first Source of Mnf 2 that it found, indepentdenly of the sorted criteria applied.

You can find a simple example of the setting that I am applying in attachec designed and, here you can see that Alteryx results does not applied the sorted criteria in the source table.

I appreciate if you can share with me your suggestions and opinions to solve it.

Thank in advances.

Laura.

[vizAlter · Accepted solution]

Hi @LauraFP — "Find Replace" tool works with String data types(s) only.

Try this solution:

"Summarize" tool will be helpful in your case:

And, then you can use a Formula tool to change the datatype back to Numeric, see this formula:

ToNumber([Total Sales])

[grazitti_sapna]

Hi @LauraFP , you can try this.

After sorting you can use tile tool based on the configuration given below and then filter the result out on that basis.

or you can simply use this.

The reason why you were getting the unwanted result is in find and replace tool it returns the value with the recent transaction which in your case was Source of manufacture for material A = 4121

i.e the second record for same material.

Another possible way is to sort and then apply unique tool on basis of material.

I hope this helps.

Thanks.

[LauraFP]

Hi VizAlter!

Thank you so much for your suggestion, it works in my analysis!

I do apologise fr my late reply, but I re-cap this work today.

[LauraFP]

Thank you so much for your reply, I really appreciate it!

I'll study your case, because it can be a sulotion too and It its a great opportunity to lear about a new tool for my, Tile.

Thanks.

[vizAlter]

@LauraFP — Thank you!

[LauraFP]

Hi  Graziti_sapna,

Solution number 2 works perfect in my workflow, thank you so much, at the end I decided for this Tile tool!