How to use a self foreign keys but not in the load ?

[#1]

Good day,

I would like to make a reference in my cross table, but on a field that have not the same name (a kind of self foreign key).

An exemple will be easier to explain.

Let's say I have the following table :

[CODE]

TblFriends:
LOAD * INLINE [
ID, Name, BestFriendID
1, Chandler, 3
2, Joey, 1
3, Ross, 1
4, Monica, 5
5, Rachel, 4
6, Phoebe
];

[/CODE]

And I want to make a chart that displays the best friends name :

I can do this by adding

LEFT JOIN (TblFriends) LOAD
ID AS BestFriendID,
Name AS BestFriendName
RESIDENT TblFriends;

in my LOAD section, but I don't !

What formula can I use in my chart expression that says "SELECT Name FROM TblFriends WHERE ID = BestFriendID_in_my_current_dimension" ?

[#2 · Accepted solution]

I reply to myself, it could help others...

FieldValue('Name', FieldIndex('ID', BestFriendID))

[#3]

table:
LOAD * INLINE [
 ID, Name, BestFriendID
 1, Chandler, 3
 2, Joey, 1
 3, Ross, 1
 4, Monica, 5
 5, Rachel, 4
 6, Phoebe
];

Left Join

Load
 ID as BestFriendID,
Name as BestFriendName
Resident table;

Above code is working

[#4]

I asked "how to do this NOTin the load" because I already know how to do it in the load.

The thing you replied is the "load solution" that was included in my file in the first message...