Create a join on two fields, one or the other

[D19PAL]

Fact:
LOAD
first_id,
second_id,
sales
FROM ******;

Left Join (Fact)
LOAD
salesid as first_id,
target
FROM ****;

At the moment i'm joining the tables by salesid = first id.

How do I create a join salesid to first_id or second_id? so i can match on one or the other ?

Thanks

[D19PAL]

Will this work?

Fact:
LOAD
first_id,
second_id,
sales
FROM ******;

Left Join (Fact)
LOAD
salesid as first_id,
target
FROM ****;

Left Join (Fact)
LOAD
salesid as second_id,
target
FROM ****;

or is there a better way?

[GaryGiles]

@D19PAL 

You would want to create a Target table that links via a combination of first_id and second_id:

Fact:
LOAD
AutoNumber(first_id&'|'&second_id) as target_id,
first_id,
second_id,
sales
FROM ******;

Target:
LOAD Distinct target_id,
first_id as salesid
Resident Fact;

LOAD Distinct target_id,
second_id as salesid
Resident Fact;

join (Target)
Left Join (Fact)
LOAD salesid,
target
FROM ****;

[D19PAL]

I see the target_ID is first_id & second_id, is this joining them? i need the join to be on one or the other.  Will this work for that?  sorry for doubting you, I just want to make sure.

Thanks

[GaryGiles · Accepted solution]

@D19PAL 

You will end up with 2 tables.  Fact and Target.  In this way, you can filter on salesid and match the Target to rows in your fact table that have the salesid in either the first_id or second_id field.  I think this will give you the most useful and flexible means of working with your data.

If you want the Target for both first_id or second_id on the same row in the Fact table, you would need two fields like this:

Fact:
LOAD
first_id,
second_id,
sales
FROM ******;

Left Join (Fact)
LOAD salesid as first_id,
target as target_first_id
FROM ****;

Left Join (Fact)
LOAD salesid as second_id,
target as target_second_id
FROM ****;

Your approach depends on your requirements and how you are going to use/present the data.

[D19PAL]

Thanks