Tableau-How to look up data to a table with repeated values and filter based latest date

Question

I need to calculate a field which is to use a common value(Hostname) from 2 separate tables.

Table A consist of value 'Hostname' - Master table (where calculated field to be created)

Table B (TechRC)consist of values 'Hostname' 'Patch Status' and 'Date' - 'Date' is unique value(Primary key) based on which Hostname values are listed(Hostname can be repeated)

The requirement is to calculate a field, 'LatestHostStatus' to populate only the 'Status' which are listed for latest 'Date' Tried Filtering using 'Date', no go also tried the formula below, but didnt help

Tried this bit in my formula to filter

IF ATTR([NODENAME])=ATTR([TechRC (TechRC)].[Nodename]) AND [TechRC (TechRC)].[MaxDate] THEN 'Overdue Patches-'+ATTR([TechRC (TechRC)].[Patch Status])

I have created a calc filed under techrc 'LatestNodes' to get Hostnames for Max data by slighting adjusting the formula given in answer below, it goes like this IF [TechRC_Date] = {FIXED [Nodename] : MAX([TechRC_Date])} THEN [Nodename] END

and tried creating another calc field (using below)under master to get status of all hosts under master including the patchstatus from techrc and it throws an error blending secondary datasource due to unsupported aggregation. I want to look up in to LatestNodes list and should return their patch status to the worksheet in master

IF NOT CONTAINS(attr([TitleIDs]), 'B') THEN IF NOT CONTAINS(attr([TitleIDs]), 'D') THEN 'NotScanned-Coverage gap' ELSEIF ATTR([NODENAME])=ATTR([TechRC (TechRC)].[LatestNodes]) THEN 'Overdue Patches-'+ATTR([TechRC (TechRC)].[Patch Status]) ELSE 'Nonoverdue' END ELSE 'NonPatchable(for various reasons)' END

Answer 1

While you won't be able to create the last patch status within the Master table (since it doesn't contain the necessary data to create it to begin with), you can create the calculated field in TechRC and use Data Blending to link the two tables.

Here is the calculated field I created in TechRC:

IF [Date] = {FIXED [Hostname] : MAX([Date])} THEN
  [Patch Status]
END

This uses the FIXED LOD (level of detail) expression to loop through all the records with the same Hostname, then figures out the MAX date for that hostname. It then returns the patch status with the latest date for that hostname.