Count Records
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Alteryx Community,
Just started using designer and would greatly appreciate any help with this task that I have spent hours with no respite!
I'm looking to feed the customer's name, car# and volume as input in the spreadsheet and get the below 4 columns as output:
1. Unique customer's name
2. Unique Car#
3. Sum volume of unique customer's names corresponding to unique Car#
4. Count of Cars, but don't want to count any two cars with the same customer's name and car# and volume net to zero.
Below is your reference for what I'm trying to do.
I used the summarize tool to group by customer and car# and sum of volume and count by car#, but I got "3" as car count instead of "1".
Please advise how to not count above highlighted rows where the volume net to zero in Alteryx. Thanks in advance for any help.
- Labels:
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @caffe2004 you're very close - here's how you can achieve the desired fields:
1) We'll need to apply a Formula expression of something along these lines to convert your volume field into numeric and turn bracketed negatives into -x format:
2) We can then Summarize, using 'Group By' [Name] and [Car#]. The difference between your approach is that for the [Car Count], we need to use a Count Distinct - a standard count will just do a general count of how many there are, rather than how many types/different cars there are, which in this case is 1. The volume will now sum as expected:
Hope this helps to get you started!
If your [Volume] field is already fine and the bracketed appearance above is just how you're displaying it in Excel then you can ignore step 1 and just skip to step 2 and use that Summarize configuration!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @caffe2004
It sounds like you are on your way and close to the solution.
Attaching a solution for how I'd go about it - it looks like the count distinct feature was what you're missing:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for your help. It works perfectly. I have an issue with another case I'm running. I'm using the summarize tool to group by strategy and contract# and sum of volume and count of railcar#, but I can't get car count as expected because there are a lot of rows have the same customer's name, car number and same ticket date, but the volume net to zero. I manually delete these rows before I put the spreadsheet in Alteryx to get the correct car count.
Attached is my workflow. SR Sept close-2 is an original file which includes a lot of rows exactly offset each other (highlighted in orange), SR Sept close-3 in desired output box is the file I manually deleted these rows which give me the correct car count.
Is there any way these rows can be deleted in Alteryx?
Thanks in advance for any help.