I've made a case statement to basically tell me when different zones are open/partially open/closed for different wells on different dates. But this only changes periodically, so there are several null values between status changes that I want to carry forward with the previous status's value. Ideally I'd want to have that in the same calculation as the case statement, but you apparently can't use cyclical arguments there so I'm okay with creating a new column to do this but I'm still having trouble filling the blanks.
A simplified version of my data set is below, with the current outputs in the '1st' and '2a' columns returning a 0 when there's nothing downhole, a 0.5 if it's partially ported, and a 1 if it's blanked. Right now, I just have in the case statement to return a null if there's no change that month for that well.
Well Date Zone In Size In Zone Out Size Out 1st 2a
101 1/2019 2a blank 0
101 2/2019
101 3/2019 2a blank 1
101 4/2019 1st port 0.5
101 5/2019
101 6/2019
101 7/2019 1st port 0
Below is the status I would like for the '1st' and '2a' columns.
Well Date Zone In Size In Zone Out Size Out 1st 2a
101 1/2019 2a blank 0 0
101 2/2019 0 0
101 3/2019 2a blank 0 1
101 4/2019 1st port 0.5 1
101 5/2019 0.5 1
101 6/2019 0.5 1
101 7/2019 1st port 0 1
Or
Well Date Zone In Size In Zone Out Size Out 1st 2a 1st filled 2a filled
101 1/2019 2a blank 0 0 0
101 2/2019 0 0
101 3/2019 2a blank 1 0 1
101 4/2019 1st port 0.5 0.5 1
101 5/2019 0.5 1
101 6/2019 0.5 1
101 7/2019 1st port 0 0 1
As I said previously, I'm using a case statement for these columns since the input zone/size data I'm given is a bit more complicated than what I'm showing here so having a secondary '1st filled' and '2a filled' columns is acceptable if necessary.
..
If anyone wants/needs it, an example of my current case statement in the simplest zone is below:
case
when ([Zone In]="1st") and ([Port_Size In]=0) then 1
when ([Zone In]="1st") and ([Port_Size In]!=0) then 0.5
when [Zone Out]="1st" then 0
else NULL
end
(In the input data, a value of 0 means it's blanked, anything else is a port size, hence the code not exactly matching the simplified data example.)
Any help that anyone could give me would be very much appreciated!!! Please let me know if you need any other info.
I think I figured out! I used this for the '1st Filled' column.
SN(last([1st]) OVER (Intersect([Well],AllPrevious([Date]))),0)
Thank you so much for the inspiration Gaia!