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:
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
Solved! Go to 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
@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
@R0M
Thank you for the accept mark.
I am thinking to submit as an idea for weekly challenge, would that be alright with you?
Thanks for your help!
Please go ahead if you think it could be a good use case for a challenge.
Romain