lookup a master list by comparing other lists , then append as "existing" or "new"

1) input a master list of items (this gets built as data collected)

2) get data from sources

3) combine data from sources then, lookup the master list, if the product is in master list append field "existing" else "new". The issue I have is that if there are products that get appended in master list are duplicated and "new" items are ignored.

ask-alteryx-lookup.yxmd

Data Challenge

Accepted answers

meghanavmr

@binu_acsthank you, I was able to download the solution and yes please would you be able to append the names which are not present in the master file to the existing master file or create a new list which loops back and saves it as a new whole list? . Also, what if every supplier file shared same attribute of data but is present in multiple files and cannot be merged. And how can we add a macro to pick these files (browse file/ folder?)

All comments

binu_acs

@meghanavmr Can you upload the input files? either export the workflow (option-export workflow) and attach or just attach the input files

meghanavmr

attached are input files

@meghanavmr I updated the workflow based on my understanding.

1. Combined the Source1 and Source using the wild character functionality in the input tool

2. Vlookup - the Combined source files and the Master files based on the field Name

3. If the Name is not present in the Master File created a flag column and updated it as "New", the name present is then updated as "existing".

do you want to append the names which are not present in the master file to the existing master file?

Let me know how you want to proceed.

ask-alteryx-lookup_updated.yxzp

meghanavmr

Yes! Thank you,

meghanavmr

am unable to open the workflow; is it possible to share the solution in any other way?

binu_acs

@meghanavmr attaching the workflow

ask-alteryx-lookup_updated.yxmd

meghanavmr

binu_acs

@meghanavmr Updated the workflow to append the new names in the same master file.

Can you elaborate a little bit more about your second requirement?

ask-alteryx-lookup_updated.yxmd

meghanavmr

I am unable to share the exact output. However, if the files that were merged (supp) had different column names and could not be merged in excel (consider each supp file separate), then we would need to have every single data source as a separate input. In such a case how would the find and replace work? There would be 3 inputs instead of 1. Secondly, what are the ways we can use a macro to pull the files into the workflow?

binu_acs

@meghanavmr You can use a bath macro to combine these source files, I have a question, suppose your input file columns' names are different but are the fields will be in the correct order? the example below, if in that case you can union the fields based on the position. let me know

File1 ID, name1

File2 ID, name2

File3 ID, name3

meghanavmr

The batch macro will not work. As the file names, column names, and the position of columns will not be the same.

for ex:

File1-name#-columnD

Fl2-name45-columnA

Filessup3-itemname-columnC

I am thinking that the files should be converted to a different format - like in json? explore the api option? Objective is get data that is in different formats , extract common values and maintain the list of common values to be compared with another set of incoming data that is not standard

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703