Missed the Q4 Fall Release Product Update? Watch the on-demand webinar for more info on the latest in Designer 24.2, Auto Insights Magic Reports, and more!

Alteryx Designer Desktop Discussions

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

Count Records

caffe2004
5 - Atom

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.

caffe2004_0-1669838107004.png

 

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.

4 REPLIES 4
DataNath
17 - Castor
17 - Castor

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:

 

DataNath_1-1669839523770.png

 

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:

 

DataNath_2-1669839602709.png

 

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!

gautiergodard
13 - Pulsar

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:

gautiergodard_0-1669839774362.png

 

 

binuacs
21 - Polaris

@caffe2004 One way of doing this

 

binuacs_0-1669846317196.png

 

caffe2004
5 - Atom

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.

Labels
Top Solution Authors