Dynamic replace based on if then statements

[Sabine_van]

Dear Community,

In my dataset I would like to dynamically replace values based on if then statements. I have included some mock data to illustrate what kind of data I have. 

The first dataset is a dataset with several characteristics per car:

My second dataset is a set of if then statements that need to be used to update my first dataset:

The first line reads as IF [Color]='black' AND [Type]='sedan' THEN [Price]='600' ELSE [Price] ENDIF

As you can see the if then statements address different columns and replacement occurs in different columns as well.

In addition, due to the nature of my real life data (for example misspellings) we need to deal with values as a contain rather than is. 

In the second line the brand reffered to is not Kia, it needs to contain the letter K. 

The desired outcome is as follows:

My thinking was to break the problem down into two parts: 1) selecting rows that are affected by if then statement; 2) dynamic replacement of the values.

The last step I can perform using the dynamic replace tool, but I am struggeling to find a dynamic solution for the selection of the rows affected by the if then statements. My actual data has many more columns and rows that could be affected and the number of if then statements that I need to apply are north of 500. 

Who can help me?

[flying008]

Hi, @Sabine_van 

1- How many rule conditions in your 2nd datasets ? as your snap show, there are 2 rows, maybe more than 500 ?

2- If there are many rules, how do you handle priority?

[Sabine_van]

The number of rules is over 500, meaning over 500 rows in the same format.

Priority is indeed an issue, so for conflicting rules we have made a variable called Priority that has a value of 1, 2, or 3.

If multiple rules apply then priority 1 goes first, then priority 2, then priority 3. 

[Biswarun]

Please consult the attached workflow. Two use-cases are demonstrated including one with conflicting rules.

[flying008]

Hi,@Biswarun 

Just like my think as dynamic replace with formula expression.  👍

[Sabine_van]

Thank you for your input! I however run into trouble when adding an additional rule:

There seems to be a conflict with another rule as it uses the same Rule1 and Rule2, but has different values.

The output does not give output for this if then statement:

When I sort the rules differently the rule for the Kia gets ignored and the jaguar rule gets executed.

Is there a possible workaround?

[Biswarun]

@flying008 Any idea why the additional rule incorporated by Sabine008 fails?

[flying008]

Hi,@Biswarun 

IMHO,  If the condition is met, the rule on the last row always takes precedence.

[Sabine_van]

@Biswarun @flying008 Would it help if a macro is used to loop over the lines? With lines I mean CarNo, so that you get all the rules applying to a single CarNo and then apply dynamic replacement through a formula. 

[Sabine_van · Accepted solution]

Dear @flying008 @Biswarun ,

I have found a solution that made it all very dynamic. I have used two batch macros to make it work: 

I used three data inputs: Input car data, Input if then statements and Input If then statements replacement.

Input if then statements is not similar to the rules described above while Input car data is.

The IF THEN statement is used to fill an empty variable called 'RULE' with the linenumber of that specific IF THEN statement 

that applies to my data line. Batch macro is grouped by linenumber of specific IF THEN statement (rulenumber). 

 

The output is a list of all data with RULE that is filled with rulenumber that applies to this line of data. 

This results in multiplication of the data as multiple IF THEN statements could apply to 1 line of data or reduction of data as no rules apply to 1 line of data.

Input IF THEN replacement is a simple file with rulenumber (that can be joined to RULE) and REPLACE and REPLACE_VALUE.

After the join it is sorted and goes into the second macro:

The second macro is grouped by CarNo and simply replaces the value using a dynamic replace (without formula).

After this macro I have placed a unique tool to remove duplicates.

Thank you for your responses, it really helped getting my thinking in the right direction!