Replace a nested If that has many if's
So i have a nested if statement that checks the status of a shipment.
"cancelled" or "storage" first using boolean, the progresses through a series of dates to see which have been added, finally defaulting to "At Origin" if it has not left yet.
Multiple IF's are not ideal, but I can't see how I get round this one.
Even if I break it down into 6 separate lines I still only need one 'Tank Status' for each line item, so would have to get the result somehow.
thanks
Best Answer
-
@DeveloperCYT That's the simplest way you can do it really. There are other alternatives to do it but I would not recommend it as it makes it less readable. Posting it here just to give you an idea.
The alternative I am providing is based on the following assumptions:
- Cancelled can occur at any point of the process
- Status changes occur in the following sequence:
- At Origin
- On Water
- At Port
- Bottler Booked
- Produced
- Storage
Step 1: Assign numeric code to each status
Tank Status
Code
At Origin
1
On Water
2
At Port
3
Bottler Booked
4
Produced
5
Storage
6
Cancelled
7
Step 2: Create numeric line items for each of the variables used your nested IF.
Line Item
Formula
ShipDate Value
IF ISNOTBLANK('Ship Date (Act)') then 1 else 0
ArrivalDate Value
IF ISNOTBLANK('Arrival Date (Act)') then 1 else 0
BottlerDate Value
IF ISNOTBLANK('Del to Bottler Date (Act)') then 1 else 0
Prod Date Value
IF ISNOTBLANK('Prod Date (Act)') then 1 else 0
Storage Value
IF IN Storage? Then 1 else 0
Cancelled Value
IF CANCELLED? Then 7 else 0
Final Value
MAX(Cancelled Value, ShipDate Value + Arrival Date Value + Bottler Date Value + Prod Date Value + Storage Value)
Step 3: Status = FINDITEM(Tank Status, TEXT(Final Value))
As advised, I don't recommend this alternative compared to what you currently have in place. Just did it to give you an idea what other options are available.
0
Answers
-
Hi, later i did something very similar to that, using formulas equalling 99,1,2,3,4,5,6 and then max of the fields, so very similar, but same result.
Thanks for confirming, much appreciated.0 -
@rob_marshall curious which of the two approaches above do you prefer and why?
0 -
I would do something very similar in assigning 1's to the booleans but then creating a line item subset that has a Total on it.
0 -
@rob_marshall Thanks
1
