Mapping with dynamic Formulas and multiple fields

[R0M]

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:

C1	C2	C3	Result
Earth	New Zealand	Earthquake	R2
Earth	South Africa	Water	R4
Ice	Iceland	Island	R2
Wind	United Kingdom	Fog	R3
Ice	Denmark	Island	R1
Wind	Japan	Island	R3
Nice	Brasil	Water	R4
Nice	Brasil	Calm
Nice	Brasil	Tsunami	R5

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 search	Classification
C1	Ice	R1
C2	land	R2
C1	Wind	R3
C3	Water	R4
C2|C3	Brasil|Tsunami	R5

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 solution]

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 solution]

@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

---

[Qiu]

@R0M 
Thank you for the accept mark.

I am thinking to submit as an idea for weekly challenge, would that be alright with you?

[R0M]

Thanks for your help!

Please go ahead if you think it could be a good use case for a challenge.

Romain