Restricting Results from a table in Bi4 semantic layer

[Former Member]

Hi

I am having an issue with a Data foundation layer that I am trying to create on a 3rd party database

There are three tables in question and for the sake of argument they are called person, personaddress and address.

Changes in address would appear to create a new record with an incrementally increasing key id in the address table, this is reflected in the personaddress table with a new record being added there as well containing the new key id but essentially in this table both records are identical.

I need to create a join from my person table to the personaddress table restricting the records in the person address table to the higher of the two (or more) records in the personaddress table. From there I can then get my address record.

The restriction I considered in SQL to make this work would be to select the top 1 row from the personaddress table sorted by the identifier in descending order but how I do this in BI4 quite escapes me.

I suspect the application that fronts up this database will do something broadly similar in the code - I just need to be able to replicate this

Thanks in advance

[Former Member]

Is there any sort of effective date? With slowly changing dimensions, which you appear to have here, there is often an effective from and an effective to column. Effective to column for the most recent address would be 31/12/2999 or some other big date (or null in some cases). If so, you could create a restriction where address.effective_to = '31/12/2999' or similar.