How to append only new data to an excel file

[Xeu911]

Hi all,

I would like to build a workflow that can add new data on a weekly basis to a dataset (xlsx format). 

Context : I use a custom version of the Alteryx Server Usage Report which has a historic of 30 days. However I would like to keep a track of the activity for a longer time frame. Also I don't have access directly to the workflow itself so I can't modify it directly.

Input will look like this : and every week there will be new data. Data have a lifetime of days.

So I'm trying to build aworkflow that will run every week to add all new runs from the Gallery into my historic excel file.

Does anyone has an idea how could I do it please ?

[gautiergodard]

hey @Xeu911 

noticed you are using some of the reporting tools to output your results - is the formatting of the output important to your solution?

if not, i would recommend using a regard output tool to create you excel output.

[Xeu911]

Hi @gautiergodard 
The format is not important at all. My issue here is for exemple : I did not have any activity of the Gallery for a week let's say but my workflow will still run to add potential new data. Then I'll have duplicated values in my output file. That's when I'm struggling really. I want to add new data, and remove duplicates that will be in the input for about 4 weeks (since it's 30 days lifetime). I don't know if my problem is clearly stated?

[gautiergodard]

Hey @Xeu911 

If i understand correctly, it sounds like you might want to add a control within your workflow, so that the outputs will only be generated if there is data to pass through?

Perhaps, you could try something like this?

In similar fashion, you could create a filter based on a timestamp, so that all values created before a certain time could be automatically filtered out and therefore avoid the duplication of values you are referring to?

[Xeu911]

@gautiergodard Only data that are not already in my output (historic file) must be append. So the date filter will not work here. What I'd like to check is : if the data il alreasy in the ouput then it must not be append. Otherwise if it's new data then it must be append.

I've tried the workflow and added a filter but I still have duplicates values in the output...

[RobertOdera · Accepted solution]

Hi, @Xeu911 

This might not be very efficient, but have you tried putting a Unique Tool or Sample Tool in your process before you output (and selecting the core fields except the primary key fields)? 

This should de-dupe your records and output only historic = new appends.

Cheers!

[gautiergodard]

hey @Xeu911 

I believe you should be able to solve this with a join. Basically, you would join your historical data to your current and anything that didnt join based on a set of common join keys would be treated as new values and get appended. 

Oversimplified example below:

[Xeu911]

Hey @RobertOdera @gautiergodard thanks to both of you for your inputs! I managed to do it using an union / unique tool at the end of the workflow. 
Cheers guys!

[RobertOdera]

You're most welcome @Xeu911!

[jelrod1]

Can you help with describing how you made it work with the union & unique? I'm needing to do the same thing. The source file is a list of "work orders" and when i'm appending the updates I do not want the same work orders being added. Only the new data.