Append unique records

[ArnabSengupta]

Use case: There is an excel file in shared drive which is used everyday by a team member. The excel file consists of issue records and comments. Everyday a team member  copy new records and paste it on the excel file (overwriting) manually (once a day). Then they manually run the Alteryx WF (once a day) which appends these new records to a  table in database.

Problem scenario: If the team member runs the Alteryx WF twice (with same existing records in excel file) instead of once a day, duplicate records will get appended on the database table.

Question: How can I avoid this problem scenario?

Thank you!

[DavidP]

Hi @ArnabSengupta 

I would add an input data tool at the start of the workflow to read the records from the database - if the database table is very large, perhaps put in a WHERE clause in the SELECT statement and only load records created in the last xx days. I'd then use a join tool to join the database data with the data loaded from the Excel file and use either the left or right output (depending on which input your Excel data is connected to.

This outer join will give you only the records that are not already in the database.

[ArnabSengupta]

@DavidP : Thanks for replying. Is it possible I can incorporate this on the same canvas ? I am trying to avoid 2 different WFs. I have attached a sample WF which is used for the current Use case.

Kindly advise!

[Qiu]

@ArnabSengupta 

If you can allow adding one column with current date data which will server as an identifier.

Then in the workflow the first run with output your intended data and one additional column with currect date.

Then user run second time, the date will be same with last one then we can do somehing to only output the "old" data only.

Just an idea.

[DavidP]

Hi @ArnabSengupta 

Am I right in say that records in the database where the Comment field is not empty have already been written from the Excel file?

So if you add a filter tool as shown below, you'll only look to join rows with empty Comment fields to the Excel data?

[ArnabSengupta]

Hi @DavidP 

That's correct, the records after the inner join is getting populated with comments and it is appended in another database 

Since the records are getting appended in the output database, there is a possibility of duplicate records (if the WF is run twice with the old records). How do I resolve this in a single WF instead of having two WFs?