Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Mapping with dynamic Formulas and multiple fields

R0M
6 - Meteoroid

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:

 

C1C2C3Result
EarthNew ZealandEarthquakeR2
EarthSouth AfricaWaterR4
IceIcelandIslandR2
WindUnited KingdomFogR3
IceDenmarkIslandR1
WindJapanIslandR3
NiceBrasilWaterR4
NiceBrasilCalm 
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 searchClassification
C1IceR1
C2landR2
C1WindR3
C3WaterR4
C2|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

4 REPLIES 4
Greg_Murray
12 - Quasar

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. 

Greg_Murray_1-1608676191581.png

 

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. 

 

Greg_Murray_0-1608675524644.png

 

Hope that helps,

Greg

 

Qiu
21 - Polaris
21 - Polaris

@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 option1223-R0M.PNG

 

Qiu
21 - Polaris
21 - Polaris

@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
6 - Meteoroid

Thanks for your help!

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

 

Romain

Labels