Sum Data in Columns by Company - Keep all the Columns

[jportello]

Hi Alteryx Family! Question that I'm having a hard time wrapping my brain around... I know I should be using the transpose and/or the summarize tool but not sure the best way to get the goal that I want. 

I have attached same data and also what I want it to look like. I basically have sent out a questionnaire to lots of companies and will be receiving their responses back in this format. I would like to get the totals by company for each of the columns that have a $ input. 

(the first tab is what I want alteryx to do with the data and the second tab is the example data)

Let me know if theres any other info I can provide! Thank you so much in advance :) 

[davidskaife]

Hi @jportello 

I've pulled together the attached workflow which should give you what you need.

You'll need to change the columns headers if required, and also include other responses as appropriate as only done it so it matched the example you provided. It will be relatively straight forward to add in more columns.

[jportello]

Thank you so much!!! This is SO HELPFUL!! I tried adding in all the other "Enter..." columns into the workflow and after the "Change to Numberic Data Type" only some of them were converted to a "Double" . See screenshot, I also included the workflow. Any idea why? 

Thanks again! @davidskaife 

[davidskaife]

Hi @jportello 

Yup, some of your data contains nulls, which were converted to empty fields and Alteryx was leaving them as strings. Try the attached, have tweaked slightly to account

[jportello]

@davidskaife Thank you so much! When I run it I get a long error that I've attached a screenshot of. I also tried adding the Multi-Field formula tool to change the nulls to 0 on the original one that you uploaded to see if that would be any different but I get the same error. 

(Also, now that I've downloaded it, would you mind deleting the workflow you uploaded in your last response? I realized that I included data I didnt mean to.)

[davidskaife]

Hi @jportello 

Workflow deleted as requested!

Regarding the error the columns its referencing are all null, so the datatype is Double. The error is coming as its trying to apply a String conversion to a numeric value.

Are you likely to have a column of no responses?

[jportello]

Thanks again @davidskaife !! There might be a column where there wont be a response necessary. The error stopped popping up when I removed the "test" data row where there was "ABC" as a response for Company #. 

Now I have run into a different issue though... the auto field tool isn't adjusting all the columns to be a numeric field so then I cannot use the summarize tool on those columns (see attached screenshot). In my example I only clicked a few of the columns that say "Enter" since it was just an example but I need all of those columns for my analysis. 

Thank you in advance AGAIN! lol 

[davidskaife]

Hi @jportello 

It looks like its due to the presence of NULLS in the data - is the 'Change NULLS to 0' not working correctly?

[jportello]

I've reuploaded the workflow with shareable data so you can see on your end. I also was trying to use a dynamic rename to remove the "Sum_Enter" from the beginning of the all the column headers but I think I'm having a hard time since theyre a string variable? Not sure but was hoping to get your thoughts on that as well. 

[davidskaife]

Hi @jportello 

Can you re-share the data as well, sharing just the workflow doesn't include it