How to prevent duplicates and ensure 'latest date' is displayed in multiple columns and from multiple data sources

[Ian Levoi]

The idea of my Spotfire Dashboard is to display the most current configuration of a machine. Below is a simplified version:

 

 

 

 

Date

Wheel Serial Number

Date (2)

Axle Serial Number

Date (3)

Car Serial Number

 

 

01/06/2021

100

04/06/2021

200

10/06/2021

300

 

 

20/06/2021

101

24/06/2021

201

27/06/2021

301

 

 

 

But it is not quite so simple because the Wheelsare moved and installed in a different Axleand the same with Axlesin different Cars..

The Date, Date (2), Date (3) are the test dates of each corresponding part. A part is tested every time the configuration changes.

This data canvas attached shows how the the test data tables combine to create the 'Current Car Config' data table.

Important: The [Wheel Serial Number] is used as a Unique Identifier to link the 3 tables.

My problem is that I need to limit/filtermy data to ensure the following:

 

Each part should only appear once in the table at any one time.

The latest configuration should always be displayed.

If a Wheeltest date [Date] is later than a Axletest date [Date (2)], the wheel is not installed therefore the columns [Date(2), [Chassis Serial Number], [Date(3)], [Car Serial Number] should be empty.

If a Chassis test date[Date (2)] is later than a Cartest date [Date (3)], the Chassisis not installed therefore the columns [Date(2), [Chassis Serial Number], [Date(3)], [Car Serial Number] should be empty.

 

I've been struggling trying to adapt the following formulas to achieve this:

Rank(baserowid(),"asc",[Wheel])=1

I've also tried using Max Date expressions which will not work either.

Please help!