Summarize but ignore empty string

[jeremyblaney]

Hi all,

I'm new to Alteryx, and have a pretty straightforward question. I'm trying to summarize my data based on a field that has two values: "Yes" and "" (empty string).

I'm summarizing using Count, but it's counting both the "Yes" values and the empty string values.

What do I need to do to tell Alteryx to not count the empty strings? Do I need to add something to my flow beforehand? Is there not a way to do a custom count formula?

- Jeremy

[MarqueeCrew]

@jeremyblaney,

One approach would be to GROUP BY the field and then to COUNT that same field.

An alternative is to count after you FILTER the data to != "".

Cheers,

Mark

[jeremyblaney]

Thanks! Is it possible to replace all empty strings with a null value so I can use the count non null feature in the summarize tool? Or is there no way to do custom count iffs?

[BenMoss]

Hey Jeremey,

Of course, you could use a formula tool to convert your blanks into actual NULL values by using an if statement.

Something like...

If [field] = "" then NULL() else [field] endif

should do the trick.

Ben

[Joe_Mako]

Sounds like you  are looking for the aggregation action CountNonBlank, that will count the record when the value of the field is not empty, it will not count an empty string and will not count a null value. When the field is a Text data type, here is where you can find the option:

[SeanAdams · Accepted solution]

Hey @jeremyblaney - happy monday!

To the points made above - there are a few ways to tackle this:

- @Joe_Mako 's method is probably most simple - using the "Count non-blank"

- You could use Mark's method ( @MarqueeCrew ) with a filter afterwards to only select the ones where the grouping field is not blank

- you could use @BenMoss 's formula to remove the blanks and turn into null, and then filter afterwards for the null (or count non-nulls)

The only other alternative that we've not yet discussed is flags - which can be as complex as you like:

- Create a formula with a new field called "IsThisTheValueImLookingFor"

      - In this formula you can put a simple condition like iif(isblank(<fieldname>,0,1)) which just puts a 1 if it's non-blank, a zero otherwise

     - if you wanted something more complex like "if this field is blank, but the other field has value AAA" you could create a flag for that

- then, downstream you can use this flag as a filter, or you can also sum up this flag field and it acts like a count

I've used this flag method lots in past lives on tough SQL queries, or to pass things down to analytical (BI) tools, and it works very well because you can count it or sum it and it works both ways, and the condition can be as complex as you like but still very simple to use downstream (easing the maintenance problems for the next person who needs to maintain this flow).

Hope that one of these solves your problem, or gives you ideas that get you to a solution - if so, would you mind marking as solved?   If you've still got questions on this - please feel free to reply.

Cheers

Sean

[jeremyblaney]

Lots of great options here. Thanks to all for the help!