How to display a value in a column based on the value in another column?

[Prog]

Hi all,

I'm pulling my hair out trying to figure out how to do what should be simple.

I have a Person table which contains all persons.

I have a Transaction table containing all transactions.

I'm making a table which includes person names, such as source person, and destination person.

Next to each of these I want another column which displays the person's gender.

e.g.

Table I am creating

Source Gender	Source Person	Destination Gender	Destination Person
Male	John	Female	Sue
Female	Jane	Female	Sam

Person table

Person Name	Gender
Jane	Female
John	Male
Sam	Female
Sue	Female

How can I populate the Source Gender and Destination Gender columns?

[chrismarlow]

Hi,

There does not appear to be anything in the Person table, that you have shared, that links John with Sue and Jane with Sam ... what is the business/logic rule behind saying one is the source and one is the destination?

Cheers,

Chris. 

[Prog]

Hi Chris,

Thank you for your reply.

I omitted further detail from the tables to keep it simple, but there are Person IDs that link it all together.

Cheers

[chrismarlow]

Hi,

So you will need to do a couple of left joins with aliasing, something like

Left Join (Fact)
Load
   PersonID as SourcePersonID,
   Gender as [Source Gender]
Resident Person;

Cheers,

Chris.

[Prog]

Hi Chris,

Sorry I forgot to mention this is for creating a sheet within an already existing app.

I can't change the ETL process for the solution.

Cheers

[chrismarlow]

Hi,

You might be able to use Only similar to this on a disconnected data model;

Cheers,

Chris.