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

[ShantanuDagar]

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

[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?

[ShankerV]

Hi @ShantanuDagar 

Some kind of screenshots or the formula used will help to investigate further what is going wrong.

Please help with those.

Many thanks

Shanker V

[ShankerV]

Hi @ShantanuDagar 

The formula with the condition might also need to rephrased.

The dataset also might be a problem.

Please find the below dataset and the output.

Some information about the formula used and the dataset will help to investigate your issue more.

Many thanks

Shanker V

[ShantanuDagar]

Thanks.

It worked by adding all validations using nested EndIF statements in 1 expression editor.