Copy previous row value based on condition

[Mark Herrmann]

Hi there,

I'm stuck with a simple problem along the lines of what is described here.

I'm aiming to copy the value of a column ([Val]) in the previous row but only if the value in another column ([Yes/no]) equals a certain value ("N").

I found a solution via a temporary column ([Previous_Val]) where I always copy the previous row's [Val]:

First([Val]) OVER (Previous([RowID]))and then in a second column I copy that only if [Yes/No]="N":

case when [Yes/No]="N" then [Previous_Val] end

All attempts to do this in one go failed in some cases I even understand why ;-) Eg this one fails, because the Previous statement only looks at [Yes/No]="N" rows

case when [Yes/No]="N" then First([Val]) OVER (Previous([RowID])) end

I've attached an exmple DXP to play with but basically my question is: Is it possible at all to do this with just one calculated column

Thanks,

Mark

[Fabian Duerr]

I think your solution is perfectly fine. And I don't know a solution to solve this with one single calculted column.

 

Having this extra column is also a plus for transparency and traceability. You can still hide it.

 

Another simple solution would be to use a data function to create this column. Like in this example:

 

https://community.spotfire.com/questions/how-use-value-n-th-row-calculated-column-within-calculated-column-itself-row-n1

 

 

[Gaia Paolini]

I have thought of a possible solution, maybe it helps. However,it generates empty strings (rather than null strings) when [Yes/No] is 'Y'. If you can live with that, here it is:

Substring(SN(First([Val]) OVER (Previous([RowID])),''),1,Integer([Yes/No]='N') * Len([Val]))

where

First([Val]) OVER (Previous([RowID])) is your original calculation

SN(..,'') around it turns it to '' when it is null (which only seems to happen on the first row)

Substring(...) does the masking:

the substring always starts at 1, but if [Yes/No] is not equal to 'N', the Integer() around it casts the False boolean value to 0, so you have a substring of length 0. If instead [Yes/No] is equal to 'N', the Integer() casts it to 1. I added Len([Val]) in case your actual [Val] is a string of length greater than 1.