File Output Creation Assistance for multiple inputs on a single tab Designer Desktop

[kimc745]

Hi All,

Once again, I cannot share the original so before I put any logic in, I took a snapshot of empty tools.

I have a workflow that produces 10 different values.  These values are numeric only (basically once I have filtered a bunch of things I have a count of records).  I have experienced issues with writing to an excel file (the same file and tab) before.

What i need to do is create an excel or csv that looks like this

Test 1 Performance Volume     5

Test 2 Performance Volume     2

etc.

They need to be on one tab in one excel file.  

Can anyone point me in the proper direction to accomplish this?

Thanks in advance

[alexnajm · Accepted solution]

Sounds like a Union of all of the different streams on top of one another! If you need labels, a Formula tool before each stream can accomplish adding those hard coded values. 

[aatalai · Accepted solution]

@alexnajm suggestion is the simplest but if you want the data in a different structure (i.e horizontally aka all in one row) then use the append tool, but you would need one for each test and hence why @alexnajm suggestion would be easier and more efficient if agnostic around data structure

[KGT]

I'm not sure if this fully covers the actual use case, but rather than separate filters, Could you create a field for "Test Performance" that allocates records based on the conditions in those filters.

I.e. a formula with IF [Test Performance] = 1 THEN "Test 1 Performance Volume" ELSEIF  [Test Performance] = 2 THEN "Test 2 Performance Volume" ELS.......

Note: There's much easier ways than the above formula (don't forget Transpose as an option) depending on what the conditions are.

Then, just a summarise with 2 output fields: grouping on the created field and count records.

[kimc745]

@KGT 

here is the larger scenarios.

I have a multi-year data set and each month that I need to collect only the records from the previous month so I have first a date filter.  Then I have 10 different scenarios to produce a metric. So I have metric name and volume.  There is no mathematical calculation to run just a 4-6 criteria filter.  Each filter produces a numeric count.  I then need to assemble that numeric count into an excel that is in the format above.   So name - value pair.  there would be a header row I need to write, aka Name  and Value for example.  Then each row underneath it would have metric name and metric value.   I am thinking ahead here, I may also need to have a reporting month field added.  I assume somehow I could capture the date from my filter and append it to the filename or make it a variable.  

I also do this for quarterly but if I can solve for the first one, then I can apply to second.  Does this change your recommendation?  I like the idea of one formula tool with all the different scenarios as it sounds more efficient than the separations that I have.  Not sure I follow on how I'd get that in an output tool.  Can you elaborate?

[kimc745]

@alexnajm 

Thanks I will have a look at that.

[kimc745]

@aatalai I will look at this as well.  I am working to see if it is feasible.  Data structure is TBD unfortunately.  I proved that we can get the metrics but waiting to hear the final format.  For now I have name/value pair one on each row for 10 rows plus some sort of header.  Once I can get the data in, I'll worry about the header. Thank you.

[kimc745]

@aatalai @alexnajm   Another thank you, I managed to solve for the name-value pair and one output file.  

This is probably not the most efficient, but I added a text input tool to add metric name and number and then appended with the value of the count tool.  Then I added a union for all of the records specifying specific order they should appear by row in a csv file.  I am going to have a look now that I can get an output to see if I can make it more efficient like @KGT suggested.  I am so appreciate of this community.  I love Alteryx but it has been on-the-job search the forum and YouTube since I was asked to learn this tool and this community always comes through for me.

[KGT]

It's great to see that you're looking at different methods! Alteryx is a platform that is best learnt on problems that you're familiar with and so you are not alone in the "on-the-job" method. The Alteryx Community is one of the strongest facets of Alteryx!

I've mocked up 2 examples, as I'm not sure if a record can match multiple tests. My above comment was based on each record only being able to match one test. I've created dummy conditions but naturally any conditions that you need can replace them.

I can mock up data pretty quickly in Alteryx, but I would say that more than half the time on this was creating the data, so a sample of what it looks like would be nice.

In the end, there are many different ways to achieve what you want and the solution by @alexnajm is the easiest to plug onto your workflow. (may need to swap your count records tools for summarizes with the option count). I hope that the options in this sample workflow give you some ideas on different ways you can implement this type of thing.

Note: I've included how I constructed the dummy data set, including the formulas which is definitely an Advanced level. If it doesn't make sense, don't worry about it, it's not needed.

[kimc745]

@KGT   This is fabulous. I apologize for the work on the dummy data. Unfortunately, I cannot share any data hence I attempted to describe.  I very much appreciate your time to do all this. I definitely see things I can use in your sample including for an upcoming deliverable.  Have a great weekend.