Left join on two mutually exclusive keys

[jorge1]

Hello everyone,

I have a bit of a weird case. I have a Tasks table and a Projects table:

• Tasks: Task ID | Task desc | Project ID | Hours spent
• Projects: Project ID | Project scope | Project category | Project status

The end goal is to be able to use Project category and Project status as dimensions to analyze tasks.

The problem is that Tasks can be matched to Projects in two mutually exclusive ways. The first obvious one is through the project ID common in both tables. However, if for a certain task the match through project ID does not happen, a match through Task desc - Project scope should be attempted.

See the below image for a better description of the issue that I am trying to solve:

Thanks a lot in advance.

[SunilChauhan]

yOU NEED TO JOIN TWO tIMES

I.E  

pROJECT tABLE

lEFT jOIN ----> Based on Project Id

Task Table 

Left join--->  Project Scope( Rename  Task Desc as Project Scope in Task Table)

Task Table

Hope this Helps

[Or · Accepted solution]

I'm not sure if this perfectly matches what you want, but I think it's similar so hopefully you can adapt it to what you need...

Table1:
Load * Inline [
Field1, Field2, Num
A, B, 1
C, D, 2
E, F, 3
G, H, 4
];

Table2:
Load * INLINE [
Field3, Field4, Num2
A, Q, 5
Z, D, 6
E, P, 7
];

Table2_Final:
Load Field3 as KeyField, Field3, Field4, Num2 Resident Table2
Where Exists(Field1,Field3);
Concatenate
Load Field4 as KeyField, Field3, Field4, Num2 Resident Table2
WHERE NOT EXISTS (Field1,Field3);

Table1_Final:
Load Field1 as KeyField, Field1, Field2, Num Resident Table1
Where Exists(KeyField,Field1);
Concatenate
Load Field2 as KeyField, Field1, Field2, Num Resident Table1
Where Not Exists(KeyField,Field1);

Drop Table Table1;
Drop Table Table2;

[jorge1]

Is there any way to avoid having to list all fields twice for each table load?

The number of columns of the tables is quite high and I would like to keep the code used in the loading script as clean as possible?

Is it possible to, for example, define a variable that contains the names of all the columns and then for each table simply load the keyfield and pass the list of column names?

[Or]

I'm not sure, but you could probably play around with it. You could also Load *, Field1, Field2 as Something, etc, and then use Drop Field on any fields that shouldn't have been loaded under that name if necessary.