How to use a single sheet to update the master sheet with new data

[Chris_]

Hi guys,

I'm new to Alteryx and was wondering if I could get assistance.

Below is my issue:

I have 3 excel files, of which I've combined to create a single master file, and output this master file to excel.

I need to update the master file with new data. I'm using the 4th excel file to update the master file. What I've done is that, I load the 4th file, compare it with the master file (using join tool) to see if the new file is having new record and existing records. I then need to add new records to the master file. This worked just fine.

But the problem comes in when I have to do another update. I have new data, which I need to paste on the 4th excel file under the existing records. I need to be able to pick up the newly pasted records, compare them with the master file, then add those records that don't exist in the master file back to the master file...and this will be a continuous process.

More like...how do I use the same excel sheet (with old and new pasted records) to update the master file with new records continuously? 

I hope I've managed to explain my issue better.

I've attached a pic of my workflow.

[ChrisTX]

Take your fields that you're joining on, and consider those to be a Unique Key.  I typically create a [Unique Key] column with a formula tool like this:

[Doc Number FI] + "|" +
[Calendar year] + "|" +
[PO Number] + "|" +
[PO Line Item] + "|" +
[Cost Center Number] 

If any of the fields in your Unique Key are type numeric or date, you may want to first create new temporary fields with a string data type, before you create the Unique Key field.

Store the Unique Key field in your Master file.  Every time you bring in new data, re-create the Unique Key field from the separate individual fields.

Then just use a Join on the Unique Key field.  If the Left input anchor is your original data, and the Right input anchor is your Master file, then the Left output anchor will be new records that you want to add to your Master file.

Chris

[Qiu]

@Chris_ 

What would be cretiria of saying a data is "old" or "new"?

If judging by it is aleady in the master file, then the left or right anchor is the one not in the master file.

[Chris_]

@Qiu thanks for your response. The old data is the records that are already in the master file. New data is the records in another sheet.

I have to take records in another sheet, compare them with the master file, if they exist in master file (they are old) and if they don't exist in master file (they are new, and would then need to be added in the master file).

The same sheet would be used to paste incoming data on a continuous basis. From this sheet, when trying to compare against master file [how do i exclude those records that have already been compared before and instead just compare the newly pasted records]?

[Chris_]

Hi @ChrisTX thanks for your response. I already have "Email" field as a Unique Key.

In the master file and new sheet, email is available and used as a unique key to compare.

For example, master file is having 50 records and new sheet is having 10 records. Join the two files and compare, 5 records from new sheet are found in the master sheet. That makes the new sheet to be having 5 new records of which needs to be added into the master list. Then the master list will have 55 records.

Now, the new sheet (with those 10 records already checked against the master file), gets 5 new records added below the already checked 10 records. This makes the new sheet to have 15 records (10 already checked and 5 new). Now, how to i go about comparing the 5 newly added records against the master file? and not compare the entire 15 records.

Currently what's happening is that, instead of comparing the 5 newly added records, all 15 records (10 old, and 5 new) gets compared against the master file. I need a way to exclude comparing the old 10 records and just compare the newly 5 added records which have been pasted below the old 10 records.

[ChrisTX]

Your question: Now, how to i go about comparing the 5 newly added records against the master file? and not compare the entire 15 records.

My response: You don't have to worry about which records you compare to the Master file.  Any records from the Join Left Output anchor will be new records.

A few other questions:

Are you running this process manually, one new input file at a time?  I'm wondering if you may need a Batch or Iterative macro.

Can you describe your process in more detail?  Will you perform the process manually, or do you want to schedule it to run automatically?

Can you provide sample input files with dummy data, and an expected output file, and describe how the process should work throughout 3 or 4 full runs of the process?

Chris

[Chris_]

Hi @ChrisTX thanks again for your prompt response. 

I've attached dummy data.

Process in detail:

I have a Master List with people and their Status (Consented, Pending, & Unsubscribed).

Another sheet with people, and this sheet doesn't have the "Status" field. This sheet, someone will paste in new data manually on a continuous basis below the data that's available. Today they paste 10 records, tomorrow they paste 5 records below the 10 records, and the next time they paste 7 records below the 15 records and so forth. [From this sheet, i need to compare it's data with the Master List, the records not found in the Master List will be given a "Status = Pending", and then add these records back to Master List]. This is being done successfully.

Problem comes in when new records have been pasted in this sheet. [When I have to do comparison, all records gets compared instead of the newly pasted records].

In the attached Dummy Data Worksheet, there are 3 sheets namely "Example Master File", "Example New Data File", and "Output File: Example Master File". The date in "Example New Data File" must be compared against "Example Master File" and Output into the "Output File: Example Master File".

When new records have been pasted in the "Example new Data File", they have to be compared {but only compare the newly pasted record}...and the process repeats whenever new records are pasted in the "Example New data File"

[DawnDuong]

hi @Chris_ 

I think it is more of a topology issue, and can be solved by including a dummy "T-1 new data" file.

If this is the very first run, then that dummy file will have no data, only the column headers that are identical to the "New data".

For everytime you need to initiate a new update, the steps are:

1) Compare the "New data" to the "T-1 new data", to identify the new records.

2) Then compare the new records (output from step 1) to update your Master file.

3) Then overwrite the "T-1 new data" with the "New data" > this is crucial so that the next time when you run the process again, it starts from step 1.

Cheers,
Dawn.

[Chris_]

Hi @ DawnDuong 

Thank you so much for your response.

You are right, it's a topology issue.