Designer| Dynamic summarize and v-lookup

Question

Hi All,

I have multiple data sets like below which I have created  using workflows in Alteryx. All datasets are firm and corresponding values.

I have a final output sheet with a master database of unique firm names , against which I need to populate the value corresponding to it.

For eg.,

Scenario 1 : the firm name Adv is only found once in all input datasets so the value 300,000 is the value pasted in the master output file.

Scenario 2: a firm is not found in any dataset, it needs to be left blank

Scenario 3 : firm is found in multiple datasets, the final value needs to be a sum of all values in the multiple dataset corresponding to its name. Eg., App final value needs t0 be (2500000 + 1600000)

Please note that the naming is a bit messy, i.e., one input may have "App limited" and another "App Ltd". This poses a problem when using "group by" in summarize tool. Attached file with input and desired output. Please can someone guide?

Input Dataset 1 Firm Value Adv Limited300,000App Ltd2500000F2F700000FD3500000GFPL200000Smith1100000Input Dataset 2FirmValue App Limited1600000Craf Ltd 500000Smith 10,100

Test.xlsx

patrick_mcauliffe · Answer

@Mj9715 Check out the Fuzzy Match tool. This does require some tuning on your part though.

You'll want to add a field identifying each of the sources so when you union them you can still identify where they came from:

Generally, after the Fuzzy Match tool, a Make Groups tool is warranted.  This consolidates A:B, B:A matches so there's only one group identifier for many keys.

Also, a good idea to change the case of all text to be the same before running.

Christina_H · Answer

@Mj9715 This is really a data cleaning problem then.  If you're confident that you will get correct matches by cutting down to the first word only you could do that in a formula tool before matching.  Or you could create a list of common variations and either remove them all before matching or replace with a standardized form (e.g. replace Limited / LTD / LTD. with LTD)

It all comes down to what works best with your data.

Mj9715 · Answer

@patrick_mcauliffe Patrick, I have union the dataset to input all fields and tried to summarize using group by firm name. The issue I am having as explained below is varying name formats. For eg., one dataset calls it AP Ltd while another one says AP Limited. The master dataset may match one of the two.. however summarize isnt considering them as the same to group by and sum it up.