Mapping with dynamic Formulas and multiple fields

Question

Hello Everyone,

I am encountering a design issue that I have not been able to solve with the Alteryx Community topics I have been through.

I am not entirely sure how to define properly my need with a title, but please see below my requirements:

I have a data set containing several columns:

C1C2C3ResultEarthNew ZealandEarthquakeR2EarthSouth AfricaWaterR4IceIcelandIslandR2WindUnited KingdomFogR3IceDenmarkIslandR1WindJapanIslandR3NiceBrasilWaterR4NiceBrasilCalm NiceBrasilTsunamiR5

I need to do a classification of each row according to a mapping table where the chosen columns, string to search and expected results are defined:

Column to search String to searchClassificationC1IceR1C2landR2C1WindR3C3WaterR4C2|C3Brasil|TsunamiR5

The classification results are displayed in my table under the [Result] column, which will be an added column not present at the beginning of the process.

The bellowing points must be taken in consideration:

* The rules are done from the top to the bottom of the mapping table, so the last ones have the priority on the others.

* I have thought about a solution to add multiples search with multiples columns, adding a character "|" to split the rule working as an AND. However, another setup may be more appropriate for an Alteryx implementation...

* I will also face some REGEX issue with my real set of data.
I have represented one case with the research of "ice" in [C1] that could be applied to "Ice" and "Nice", but I did not find out how to mix the REGEX and the multi formula with multiples columns.

If you have any ideas/best practices/solutions in mind to solve/redesign this mapping, please do not hesitate 😊.

I wish you all a good festive season.

Romain

Greg_Murray · Accepted Answer

Hi @R0M,

Here is the approach I would take.

First I would restructure your rule table so that the rules that have multiple conditionals are on separate rules and I would also apply weighting.

Once you have that you can follow the steps in the workflow below to process the rules against the records. The jist involves pivoting the record set and then joining it to the rules to evaluate which rules are met by each record. We sum the weight for each record/rule pair to ensure all conditions are met for multi-part rules. Lastly we take the greatest classification match for each record.

Also, I had to make a couple adjustments to the strings to search in order to get the regex match to work properly.

Hope that helps,

Greg

Qiu · Accepted Answer

@R0M 
The AND condition is very challenging.

* The rules are done from the top to the bottom of the mapping table, so the last ones have the priority on the others.
I assigned priority by RecordID

* I have thought about a solution to add multiples search with multiples columns, adding a character "|" to split the rule working as an AND. However, another setup may be more appropriate for an Alteryx implementation...
This is very chanllenging part. I use Sum to identify
* I will also face some REGEX issue with my real set of data.
I have represented one case with the research of "ice" in [C1] that could be applied to "Ice" and "Nice", but I did not find out how to mix the REGEX and the multi formula with multiples columns.
I use Contains Function wiht CaseSensitive option