I have 2 tables in a webi report.
In table 1 I have a start date and the manager name, in some cases the managers name will be blank.There is also a reference number for a job position.
In table 2 I have the reference number of a job position and a start date and end date.
I want to be able to create a variable that will show me either the managers name or blank if there is not one, for the job reference, based on the date range in table 2 (start date/end date), it should look at table 1 and determine from the start date in table 1 who the manager was for that job reference number.
Can anyone recommend how I can achieve this.