Left Join and Max Date

[LS67]

Hi everybody,
I have a table looking like this (Ref% is referreing to a Key number):

I would like to create a new field "Good_Ref" based on the value of "Ref_Ava" (in the script, you will see the field "OrderNumber" i/o "Ref_Ava") for each set of data based on "MasterKey" and considering the following conditions :

- If multiple trade dates : Good_Ref = Ref_Ava where Role = Market or Client AND Trade Date = Max Date for each set of data with the same “MasterKey”

- Else : Good_Ref = Ref_Ava where Role = Market or Client. If 2 references have a Role = Market or Client but two different ISIN for a same MasterKey, than I want to keep both lines and references.
To illustrate, I wish to have in result this table :

However, for now, I have the wrong reference when trade dates are different :

Here after you can see the script I wrote in the Data Load Editor :
[Data]:
LOAD
*
FROM TABLE (qvd);

[TABLE_REF]:
LOAD
[MasterKey],
[OrderNumber] AS [Good_Ref],
[ISIN],
[TradeDate],
Resident [Data]
where ([OrderRole]='Market’ or ([OrderRole]='Client’));

Inner Join ([TABLE_REF])
LOAD
date(Max([TradeDate]),'YYYY-MM-DD') as [TradeDate2],
[MasterKey],
[ISIN]
Resident [Data]
Group by [MasterKey], [ISIN];

[TABLE_REF2]:
NoConcatenate
Load
[MasterKey],
[ISIN],
[TradeDate2],
[Good_Ref],
Resident [TABLE_REF];

Left Join [Data]:
Load
[MasterKey],
[ISIN],
[TradeDate2],
[Good_Ref],
Resident [TABLE_REF2];

Drop Table [TABLE_REF];

I would be happy if someone can help on this.

Thanks in advance,

Laetitia

[sidhiq91]

@LS67  Sorry I am not able to completely understand your requirement. Could you please elaborate and also provide some sample data for us to look into it?