Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Limit csv columns per row by row value

stapuff
9 - Comet

 

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.

 

Example: 

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.

 

Stapuff

 

excel-range1.png

4 REPLIES 4
danilang
19 - Altair
19 - Altair

Hi @stapuff 

 

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

 

WF.png

 

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

 

Results.png

 

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

 

 

 

Dan

stapuff
9 - Comet

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.

 

Stapuff

danilang
19 - Altair
19 - Altair

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

 

Dan

 

 

stapuff
9 - Comet

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

 

Stapuff

Labels