Multiple formulas in 1 formula tool only giving output corresponding to last formula

Hey guys,

I am working on a dataset in which we need to input a new column and add attribute in that column as per some validations.

Example: If there is 1 validation and 100 rows fit into that validation, then attribute "ABC" should be input in that new column

Then comes validation 2, if 22 rows fit into that condition, then attribute "XYZ" should be input.

For the rows who don't fit in any, the column cell must remain empty.

There are 70 validations.

I have used formula tool to add a new column and then If Then Else condition to specify attribute.

Then adding all validations 1 by 1 in the same formula tool by selecting the created column and again If Then Else condition in the new expression editor.

But I am facing a weird issue, the result is showing only the result corresponding to the final formula. All the earlier ones are getting ignored.

I swapped 1st and 3rd formulas and then also it showed results corresponding to only last formula thus validating my doubt.

Any solution for this?

Thanks

Help

Accepted answers

All comments

ShankerV

Hi @ShantanuDagar

We can also use Find and Replace tool to overcome the issue.

If you are able to share a small sample dataset and few logic to be applied.

I can build a workflow to work and the same can ben extended to your huge dataset.

Many thanks

Shanker V

JamesCharnley

Hi @ShantanuDagar

There could be another way to do this as @ShankerV said, but can you share an example of the final formula out of interest? It sounds like the formula is overwriting the previous ones so it could be as easy as changing what is coming after the Else.

ShankerV

Hi @ShantanuDagar

Here is an example where the formula overwrites the value.

As you have more than 100 rules, split into 3 formula's any condition gets satisfied will overwrite the previous value.

Input was

Many thanks

Shanker V

ShankerV

Hi @ShantanuDagar

You can rewrite the formula like below to understand how many condition it satisfies.

Post that, which condition you need to give importance, you can do the necessary modification as per business requirement.

The solution depends on the dataset, condition and the expected output looked for.

Input was:

Many thanks

Shanker V

ShantanuDagar

Hey, @ShankerV

Thanks for suggestions, but I still have 1 query.

I used 3 separate formula tools to the input file with 1 validation in each. Then used a filter in each to filter in only the rows which aren't empty in the new column (Break type) with attribute.

Then used a join multiple on all the 3 filtered files to join on primary key.

For most of the IDs, there is only 1 break type per ID: Either Break type1 or 2 or 3 per primary key. Except for 3-4.

So, this should showcase that most of the IDs are getting validated with only 1 condition and does not pass through other conditions, so shouldn't be overwritten by the next formula.

Then what is going wrong?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703