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|
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:
I've also tried using Max Date expressions which will not work either.