Alteryx Designer Discussions

It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
SOLVED

=Countif

Highlighted
7 - Meteor

I appreciate if this question has been asked before but I was wandering if I can get a little more in debt  solution,

1. I have a formula in excel =COUNTIFs(A:A,A3,B:B,B3,C:C,C3) which generates  new column with a result value and I want to apply the exact formula in Alteryx, Fairly new and I appreciate a some detail and steps

Highlighted
16 - Nebula

@dmckee The count records tool is the best tool for counting. I've included an example to show you a couple different ways to do the "if" part, depending on how your input data looks. You basically just need to get your data set down to all the records that match (either through a filter, join, or something similar) and then use a count records to count the number of records. You can also use the summarize tool for the counting part, but the only problem is that if there are 0 records, it won't return 0 (it will just be blank).

Highlighted
7 - Meteor

Thanks for your respond Patrik, I wasn't able to open the Workflow for some sort of set up I have ( your workflow saves in my machine and Altreyx is running on different machine and no access ),

will you be able to let me know he solution here please

Highlighted
16 - Nebula

@dmckee

On the left side, my input file looks like this for our example:

 Field1 Field2 Field3 1 red Monday 1 blue Tuesday 1 purple Wednesday 1 red Monday 1 purple Wednesday

Then i have a filter tool with this logic (with is applying the if part like excel):

`[Field1]=1 AND [Field2]="red" and [Field3]= "Monday"`

Then the orange counter tool just counts the number of records.

On the right side, I use another text input to hold my inputs:

 Field1 Field2 Field3 1 red Monday

This could easily be turned into an app to grab data from a user. Then I use the purple join tool to apply the if logic (so only the matches come out of the middle J). The counter tool then counts the number of records again.

Hope that helps!

Highlighted
7 - Meteor

Patrick thanks for your very detail explanation,

question ( and maybe because I didnt explain before or didn't quiet understood the solution.

I wouldn't know what they would be equal to, column A, B, C could return any value.

in your example [Field1]=1 AND [Field2]="red" and [Field3]= "Monday" But I don't know what it would be equal to.

how would you deal if you didn't know it is = "Monday"

Highlighted
7 - Meteor

Any values not known at this point

Highlighted
16 - Nebula

@dmckee Great question! Can you share a picture of what your excel looks like? I'll take a stab and guess it looks something like this:

Column D is the formula you posted in your original question. In that case, I would use a summarize and group by your three fields (columns A,B,C) and a count of any one of the fields. Then join the count field back to your original data set using all three fields as joint fields. I use the recordID tool and sort tool to keep the row order.

Highlighted
7 - Meteor

Hey Patrick, this is great thanks, your guess regarding the sheet B,C,D is spot on however column A it is list of accounts and could go up to thousands ( sorry that I cant share the screen), sometime it could be same but still thousands.

considering that would your solution still work?

Highlighted
16 - Nebula

@dmckee That won't be a problem at all! If processing time is your concern, I sent a million fake records through the process I created and it took less than 10 seconds.

Highlighted
7 - Meteor

Thanks Patrick I think I got the result of Column D  correct however I am still not clear on column A, I should have all the accounts populate??

Labels