How to output same IDs satisfying many condition using nested ifelse

Question

I have a dataset which has 1000s of records.

Every record needs to pass through a set of validations to determine the break type and input the corresponding type to a new column.

In dataset, there can be records with same primary key. Like ID 1 can be repeated 3 times for 3-4 potential break types.

Now nested IFELSE statement is used in formula tool to validate and output the break type using THEN value and ELSE is "" at the end.

And IFELSE is a top to bottom condition. So when ID 1 can have 3 break types - X, Y, Z. The nested IFELSE is outputting only the 1st break type as soon as it comes in all the 3 records. I want all 3 in output file in different row with break types written in the new column created "Type". This process must be followed on all records -

Pass a record on all validations. The first validation which gets satisfied is the output with the THEN value in the new column "Type". If the same ID record is present in input and enters the validations, then skip the validation which is already satisfied and return the 2nd validation which gets True with the THEN value in the earlier column "Type". And so on for 3rd etc..

Basically want an added feature in the flow to skip the validation which is already satisfied for the same ID and search for next one and then output that as well.

Might be difficult to send the dataset as it's confidential. Would love to have a piece of your mind.

Thanks

ShantanuDagar · Answer

@Felipe_Ribeir0

Hey, I think you misunderstood.

1 ID (Primary key) has potential of passing (Satisfying) many validations out of like 100 validations which are inserted using nested IFELSE THEN ELSEIF THEN ELSEIF THEN ELSE ENDIF..... statements.

Now in this case, if that ID satisfies first condition then the loop ends instantly and moves to next ID.

If the same ID enters loop again, then again it will end the loop on first statement itself as it is getting satisfied right there.

But in this case, we are missing a lot of different validations which it can satisfy down in the loop which we want for analysis.

So, we want an ID enter the loop, if it gets satisfied on 1st validation then it output the validation Name in a new column named "Type" but instead of exiting the loop, moves down to next validation. If next is false, then keep on moving down until next is true and then again output that validation Name in the earlier column named "Type" but as a new row and then so on.

Example output might look like this:

ID                    Type                                               1000 ID passed through 3 validations which had IF..THEN values as XYZ, ABC, PQR in column "Type". ID 2000, 3000 passed only 1 validation each.

1000                XYZ

1000                ABC

1000                PQR

2000                ABC

3000                MNO

I have 1 solution in mind but it's super lengthy. Basically using 100 filters to data source. 1 custom expression in each filter and 1 formula attached to each of them to add Type corresponding to True values and Then a union and a Unique on ID and Type simultaneously. It should give the right answer but if there is any better solution ?

Felipe_Ribeir0 · Answer

Not sure if i understood, but maybe you could adapt an idea like this one.

The idea is to filter just the first occurrence of the ID to pass through the process.

How to output same IDs.yxmd