=Countif

[dmckee]

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

[patrick_digan]

@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).

[dmckee]

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

[patrick_digan]

@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!

[dmckee]

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"

[dmckee]

Any values not known at this point

[patrick_digan]

@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.

 

[dmckee]

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?

[patrick_digan]

@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.

[dmckee]

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??