Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?


Limit csv columns per row by row value



I have 3 sql inputs that are unioned, sorted by OrdNum & Position then output to csv (no header row). The example below is a simplified version of my data.


The csv needs to have a specific number of fields per row. Current output and required output is shown.



Any row with Position=1 would have 4 fields (field1-field4) Any of the 4 fields could be blank or contain data

Any row with Position=2 would have 6 fields (field1-field6) Any of the 6 fields could be blank or contain data

Any row with Position=3 would have 3 fields (field1-field3) Any of the 3 fields could be blank or contain data


In the image below I indicate the fields that need to be removed by {XXX}.  I could hard code that in if that gives a possible solution.


Any thoughts would be appreciated.






Hi @stapuff 


Here's a workflow that gives what you're looking for. 




The top branch uses a formula to build the comma separated strings based on the Position number and including the trailing comma.  The output tool is configured to write a CSV file with no delimiters (\0) so it essentially writes every line as is, separated by a carriage return.  If your requirements ever change, you'll need to manually edit the formula to change the output


However, I noticed that your output consisted exactly of the fields that where not null.  If that's the case, and not just a coincidence, the bottom branch is completely dynamic and will handle any number of columns on any row.  It starts with a transpose that places all the fields that you care about into 2 columns, Name(field names) and Value(field values), grouped by OrdNum and position.  The filter strips out any null values

and the Summarize builds the comma delimited string, incl the trailing comma. 


Both give exactly the same output, namely




but the dynamic version should adapt to your actual data with very little modification, probably limited to the Group By fields in the Summarize tool






Thanks, @danilang for your response back.


I went with your top example.


Is there a way to maintain fields that have comma's already in it?  I know the example did not show the use of comma's.




Hi @stapuff 


You'd need a prestep that looks for comma's in the 6 main fields and adds double quotes(") before and after the item if found.  The attached contains a Multi field tool on the top branch that does just that






Excellent.... I appreciate all you helped me with.