Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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?
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 ****;
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
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.
Thanks