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.
Solved! Go to Solution.
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.
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.
@Yoshiro_Fujimori @Luke_C Sample data attached - thank you both
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.
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.
This is exactly what I needed - thank you very much