previous row value in data table

[Manisha Tiwari]

Hi All ,

Please find attached sample data . "My data table" file is my original data table in spotfire and "expected data" is the expected columns .My requirement is restricted to data table only.I can not use other visualizations.

 

Thanks

[archie rajput]

you can do this using python scripting.You can iterate over the rows and can store the previous value in local variable.And then after storing the previous rows in variable you can add the rows in the data table visualization.

[Erik Jalsborn]

Hi,

One approach might be:

 

Insert a calculated column "RowId" with expression "RowId()"

For each column you want, e.g. "A Qnty", insert calculated column with expression: "First(FirstValidBefore(If([Type] = "A", [Qnty], null))) OVER Previous([RowId])"

 

For the short data set in your example pictures it looks right but you should probably double check that it results in what you actually want.

[Manisha Tiwari]

what is "FirstValidBefore" here .

[Erik Jalsborn]

FirstValidBefore is a method for null value replacement. From the help:"Substitutes null values in a column with the first valid value before the missing value." 

[Manisha Tiwari]

I don't see this method in spotfire6.5. Is this present in custom expression

[Anders Gavare]

I think this was added in Spotfire 7.5.

[Manisha Tiwari]

I have to find a way to do it in Spotfire 6.5 .

[Sean Riley]

Here is a solution that works in all current versions of Spotfire:

 

Column 1: RowId

 

Expression: RowId()

 

 

Column 2: Group

 

Expression: Max(Rank(If([Type]="A",RowId()))) OVER (AllPrevious([RowId]))

 

 

Column 3: Group Shift

 

Expression: SN(Min([Group]) OVER (Previous([RowId])),0)

 

 

Column 4: A Qnty

 

Expression: Min(If([Type]="A",[Qnty])) OVER (Previous([Group Shift]))

 

 

 

And just repeat Column 4 structure for A Price and A Time.

[Manisha Tiwari]

Thanks a lot. This is working perfect.

[Sean Riley]

Cool! Glad that worked. You can mark that answer as correct by clicking the checkmark below the voting buttons so that the forum knows your question is closed.

[Dylan Novakowski]

I believe I've found a more robust method.

 

This solution gives the previous [Qnty] over the same [Type]

(so this works for all types, not only when [Type]="A")

 

 

GroupId

 

(DenseRank([Type]) * 1e5) + DenseRank([Time])

 

 

PreviousRowQnty

 

Avg([Qnty]) OVER (Previous([GroupId]))

 

This isalmostright, but the value in the first 'B' type will be the last 'A' value

 

 

 

 

PreviousQnty

 

If([GroupId] != Min([GroupId]) over ([Type]), [PreviousRowQnty])

 

 

 

 

 

 

 

Type

Qnty

Time

GroupId

PreviousRowQnty

Previous Qnty

 

 

A

150

10:29:00 AM

100001

 

 

 

 

A

100

10:30:00 AM

100002

150

150

 

 

B

200

10:31:00 AM

200003

175

 

 

 

C

300

10:35:00 AM

300004

200

 

 

 

A

250

10:36:00 AM

100005

100

100

 

 

B

200

10:42:00 AM

200006

200

200

 

 

A

175

10:43:00 AM

100007

250

250