Sumarize and update output

[cgonca]

Hello, 

I am new in Alteryx, and I didn't found any tip in how to solve this problem. It seems simple, but I am having a hard time finding an answer.

I have a database that is updated daily, and this data contains a bunch of products with "open" and "closed" status. I don't keep track of what date the status change from "open" to "close". So everyday I need to have a static picture of the data base to count the "open" status.

My idea was to have Alteryx use the summarize tool, extract the count in an output file with the date and the volume of "open" status, creating a time series which I'll need to use.

Basically a sketch of my idea and the result format is something like this:

Please, can someone help?

Thanks

[MarqueeCrew]

@cgonca,

What you will do is read the database (why not include Status in the count?) and get the current date.  That is input #1.  Then you can read your stored historical data  and UNION the results of new counts with old counts.  Next SORT the data.  The sort does two (2) things.  First, it assures you that the order is ascending.  Secondly, it requires all input data to be read from the historical data.  When the sort completes, Alteryx can safely overwrite the original dataset.

For added safety, you can use a UNIQUE tool in place of the SORT to remove any duplicate DATE/STATUS records.  You might need this in the event that you should run the workflow multiple times during a particular day.

Cheers,

Mark

[nick_ceneviva]

Something like this should work if it runs everyday.

- Input tool would be your database connection

- The summarize tool would group by the status and then count the number of products

- Use the Formula tool to add today's date (DateTimeNow()) function

- Use the Filter tool to only get the row where the status is Open ([Status] = "Open")

- Use the select tool to drop the status column, and convert the date column from datetime to date

- Insert into the Excel file using the output tool

Hopefully that helps but let me know if this doesn't quite do what you are looking for!

[cgonca]

Do you know if I update the database tomorrow, will this setup keep the results from today?

[nick_ceneviva · Accepted solution]

If you configure the output tool correctly, it will keep the results.  There should be an "Append to Existing" setting that will just keep adding the daily results.

[cgonca]

So, i test this solution and the file is not appending. I tried 2 configurations. Maybe I am doing it wrong!

It's configured to "Append Suffix to File/Table Name" and I already tried "Prepend Prefix to File/Table Name".

Am I configuring something wrong?

[cgonca]

I found it. I configured to output a .qvx but when I changed to .xlsx it worked fine.

Thanks

[cgonca]

Thanks Mark,

I don't think I manage to do it correctly, but the solution taught me a few new things.