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

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 Wheels are moved and installed in a different Axle and the same with Axles in 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/filter my 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 Wheel test date [Date] is later than a Axle test 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 Car  test date [Date (3)], the Chassis is 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!

 

Attachments

AttachmentSize
Image icon current_car_config.png71.34 KB
Login