Sumif and countif deployment to get values based on the column text

Question

Hi All,

The Uniqe column (Column B)  contains the full path starting from the root folder till the file, each file have a corresponding value in the total match. I want to do sum of the values of the files for  in each sub-directory, for example if we have  the following path

file 1.txt c:\a\1.txt (10)

file 2.txt c:\a\2.txt (10)

file 3.txt c:\b\3.txt (5)

then the subdirectory a total value should be 20, b total value should be 5 and c:\ value should be 25. I can do that in excel using sumif and put the formula as [=SUMIF($B$2:$B$27,B2&"*",$A$2:$A$27)] as in the screenshot attached so the formula will be to count every value included in that directory and all the other subdirectories.

I want to do sum to the values in the total match column based on the value in the value in the Unique column as follows.

How can I do similar formula in Alteryx, I have attached the sample excel file with the formula for your reference. Please feel free to put a comment if my question is not clear enough.

Alteryx community sample.xlsx

MarqueeCrew · Accepted Answer

@mkhaled

I assume (often a big mistake) that you don't have an exhaustive quantity of records.  I would create a CARTESIAN product of all of the records first.  Use an APPEND FIELDS tool (careful to ALLOW ALL APPENDS) and bring the input data into both the T & S  anchors.  Next I would use a FILTER tool with this expression:

Contains([Source_Uniqe],[Uniqe])

Now you can SUMMARIZE with a GROUPBY Uniqe and Sum the [Source_Total Matches] field.  This should get you your desired results.

Cheers,

Mark