using Multi row formula ??

[stj1120]

hi all,

I have a data set where it includes materials, plant, and stock values. 

Each material can be present on multiple plants and each plant can either have the stock quntity or null. I have few rules in validating the results in telling which materials are active and which are inactive.

1. If the material is common in the multiple records but plant number varies and has the stock quantity in either one of the records then the value must be 1. 

2. If the material is common in the multiple records but plant number varies and has the stock quantity in all the records then the value must be 1.

3. If the material is common in the multiple records but plant number varies and has the stock quantity is null in all the records then the value must be 0 

3. If a single material is present in one record and if the stock quantity is null then 0 else 1. 

Here, 1 is active and 0 is inactive. 

I tried using the multi row formula by implementing all the above three rules but, not getting the output as expected. I attached the screenshot of the output. 

Please check and help me in getting the right values. 

Thank you. 

Best regards,

Teja.

[Prometheus · Accepted solution]

@stj1120 In the workflow, I first added a RecordID tool for the end of the workflow. Then I used a Summarize tool to count instances of the different values in the "material" field then filtered them on instances > 1. I brought those into a Join tool. After the L output anchor, I used a Formula tool to give create a field called "Result" and made it equal 0. These are the records where "material" only occurs once in the dataset. In this particular dataset, it's just one record. After the J output, I used two Multi-Row Formula tools. The first creates a field called "Result" and identifies records where "material" = "material" one row down with a 1, otherwise it is 0. The second identifies where "material" = "material" one row down AND "plant" does not equal "plant" one row down AND "stock" or "stock" one row down are not null. Conditional expressions already tend to get complex, but when you use them in a Multi-Row Formula tool, you're adding another level of complexity. After that, I unioned the Join outputs, sorted on RecordID, and deselected RecordID. I hope this gets you what you're looking for.