SUMIF Assistance - Formula Tool

[aanderson99]

I am trying to match a large amount of data and identify offsets. We have SUMIF functions for this in excel I need help with converting into Alteryx. 

This formula is for our duplicate identifier (which identifies if one item has a "matching offset")

Text: =IF(SUMIFS(D:D,D:D,-D593,C:C,C593)<>0,"Y","N")

And this formula is simply for the text direction of "Match" or "Partial Match" - I'd like to get both of these functions into Alteryx for mass, aggregated data comparisons:

Text: =IF(G593="Y",IF(SUMIFS(D:D,C:C,C593,G:G,"Y")<>0,"Review for Partial Match","Match"),"No Match")

Thanks in advance to any and all who are able to assist. 

[Luke_C]

Hi @aanderson99 

It'd be helpful if you shared sample data and expected results. Typically you won't be able to accomplish all of this in one formula tool SUMIFS usually require summarize tools, joins, filters, and formulas to recreate. 

[Yoshiro_Fujimori]

Hi @aanderson99 ,

I am not sure what you want to achieve,

but if you want to identify the duplicates in a table, there would be other approaches than checking the sum.

1. With Summary tool, count the number of rows for each "Data" and "Outage" value.

2. With Formula tool, if the count = 1, set "Y", otherwise, set "N" to a new column "Duplicate Identifier"

In the table below, [a, 1] is duplicate.

With this workflow

Output

If this is not what you want,

please provide the sample input data and the expected output data.

[aanderson99]

@Yoshiro_Fujimori @Luke_C Sample data attached - thank you both

[cjaneczko]

If you are only trying to identify duplicates and if there was or wasnt an outage, you can use the summarize tool to Group by Data then sum the Outage. Anywhere there is a 0, it was a duplicate, any where its a -1 or 1, there wasn't.

[Yoshiro_Fujimori · Accepted solution]

@aanderson99 ,

Here is one way of doing this.

Workflow

Output

TransactionID is added to sort the order.

Formula tool

Duplicate Identifier = 

IF [Count] > 1 THEN "Y" 

ELSE "N" 

ENDIF

Match Identifier = 

IF [Sum_Outage] = 0 THEN "Match" 

ELSEIF [Count] = 1 THEN "No Match" 

ELSE "Review for Partial Match"

ENDIF

I hope this matches with your business case.

[aanderson99]

This is exactly what I needed - thank you very much