assigning rank

[stj1120]

hi all,

I have an inventory dataset and I need to assign rank to each product based on the presence of stock/demand/receipts/inspection etc.

Here the product can be repeated and present in one plant or in multiple plants. 

I need to assign either 1 or 0 as the rank (Result). 

1 --> If a product is present in the same plant or on different plants and at least one of the measure column contains a value in of its records then then we assign a value 1. if any one record in this measure columns is null and the other records has a value then 1 should be assigned.

I'm able to get the right result for the below cases. 

I'm unable to get the result as 1 for the below case for the two records. 

0 -->  Assign 0 if there is production in one plant or on different plants or in the same plant but all measurement columns are null.

I'm able to get the right result for the below case. 

Please check and help.

Thank you.

Best regards,

stj.

[ed_hayter · Accepted solution]

I looked at combinations of site and product: if the combination has more than 1 non null measure then i assigned it rank 1 else 0 and joined back on to original data to keep row structure.

[stj1120]

@ed_hayter  thanks for the quick response and providing the right solution. 

but, how do we achieve this using multi row formula tool? Please tell me. 

[ed_hayter]

In this instance I would not recommend the multi-row just because your conditional clause has to look across so many columns. And the true instance might be before or after (or both) other cases so there is more logic that would need to be applied to look in both directions for any instance of a non-null value. Add in the fact that you can have varying site product combos (i.e. product 1 might be made at 6 sites, whereas product 2 only 3) and thats another layer of complexity to consider.

All that is to say I would strongly recommend against trying to do this with one multi-row formula tool (if you only want to show an end user one tool, wrap the other tools in a standard macro and name it ranking macro).