Jump to content
We've recently updated our Privacy Statement, available here. ×

Aggregating Data from same value from different row in Spotfire


Emmanuel Chukwunyere 2

Recommended Posts

I've some clinical data that needs some tricky calculations.

I have a column called "LabCode" and another "Days" which is my aggregate data/Measure.The third column is my expected output after the aggregations.

The labcode are the high (H) or Low (L) dosage from the test results from an individual on the trial.

The doctors want to know how many days the person was on low dosage before high dosage and vice versa.

I want OVER Function expression that can show the results in yellow highlights.

Thanks for your help.

Link to comment
Share on other sites

  • 1 month later...

I am not sure how is your complete data but if dataset has only these two columns then this can be achieved. If there are more columns, that will need to be adjusted in over statement. This can be achieved through some calculated columns

 

2. Data > Add calculated column... ### row id for each row

Column name: ID

Expression: RowId()

3. Data > Add calculated column... ## get labcode for previous row on the current row

Column name: PrevCode

Expression: First([Labcode]) OVER (previous([iD]))

4. Data > Add calculated column... ## get labcode for next row on the current row

Column name: NextCode

Expression: First([Labcode]) OVER (next([iD]))

5. Data > Add calculated column... ## get days for next row on the current row

Column name: NextDays

Expression: First([Days]) OVER (next([iD]))

6. Data > Add calculated column... ## get only next row days where current and next lab code is different. Then display the same over all previous rows with no data till there is a valid value

Column name: B

Expression: FirstValidAfter(case when [Labcode][NextCode] then [NextDays] end)

7. Data > Add calculated column... ## get only previous row days where current and previous lab code is different. Then display the same over all next rows with no data till there is a valid value

Column name: A

Expression: LastValidBefore(case when [PrevCode] is null then [Days] when [Labcode][PrevCode] then [Days] end)

8. Data > Add calculated column... ## Expected result

Column name: ExpectedResult

Expression: - [A]

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...