Join two tables based on condition in the second

[#1]

I've got two tables Peopletable and Hourstable like this:

Peopletable

Project	Person	Product	Role
A	Anna	Car	Manager
B	Anna	Train	Consultant
A	Johan	Car	Consultant
B	Johan	Train	Consultant

Hourstable

Product	Manager	Consultant
Car	1	3
Train	4	7

I'm now constructing a report where you select a person and then see how many hours that person has accumulated per project. So that selecting "Anna" the output would be

Output

Project	Hours
A	1
B	7

I've got all the formulas working but I haven't found how to merge the tables. In the script I would like to do something like this, but that is not possible.

Peopletable:
LOAD
Project,
Person,
Product,
Role
FROM X;

Hourstable:
LOAD
Product,
Manager,
Consultant
FROM Y;

LEFT JOIN (Peopletable)
LOAD
Product AS Product
Manager AS Hours
RESIDENT Hourstable WHERE Role = 'Manager';

LEFT JOIN (Peopletable)
LOAD
Product AS Product
Consultant AS Hours
RESIDENT Hourstable WHERE Role = 'Consultant';

How do I correctly write this script? Thank you in advance.

[hic]

I would use a Crosstable instead of the Joins....

Peopletable:

Load ... From X ;

Crosstable (Role, Hours, 1)

Load Product, Manager, Consultant From Y ;

You will get a synthetic key, but this is not a problem (as long as you don't have more tables.)

HIC

[adiarnon]

can you explain me the -

"WHERE Role = 'Consultant'"\ WHERE Role = 'Manager';

in the Hourstable there is no column like Role

i didn't get what you are trying to do....

[#4]

Hi,

Yes, you could use Crosstable to solve your problem. If you still use JOIN then try as below:

TmpTable:

Load * from peopletable;

leftjoin

load * from hourstable;

Load *,

if(Role = 'Manager', Manager, Consultant) as Hours

resident TmpTable;

drop table TmpTable;

[sujeetsingh]

Why are you limiting to scripts,they might make your model quite complex.

----Load all data from the source

----Append them to the required linking with keys and all that

----now try to produce the same in UI by different objects

thanks,

[#6 · Accepted solution]

I would join the data like below.

Peopletable:

LOAD * Inline [

Project, Person, Product, Role

A, Anna, Car, Manager

B, Anna, Train, Consultant

A, Johan, Car, Consultant

B, Johan, Train, Consultant

];

Hourstable:

LOAD * Inline [

Product, Manager, Consultant

Car, 1, 3

Train, 4, 7

];

Temp:

LOAD Product, 'Manager'    as Role, Manager    as Hours Resident Hourstable;

LOAD Product, 'Consultant' as Role, Consultant as Hours Resident Hourstable;

left Join (Peopletable) LOAD * Resident Temp;

DROP Table Temp;

DROP Table Hourstable;

With this I will have a joined table like below

Regards,

Sajeevan

[#7]

You must convert your horizontal HourTable to a vertical versión:

HoursTableTmp: 
LOAD 
Product, 
Manager, 
Consultant 
FROM Y; 

HoursTable:
NoConcatenate LOAD
Product,
'Manager' AS Role,
Manager AS Hours
Resident
HoursTableTmp;
Concatenate LOAD
Product,
'Consultant' AS Role,
Consultant AS Hours
Resident
HoursTableTmp;

Drop Table HoursTableTmp;

[#8]

Thanks for your suggestion, I went for this albeit a little modified since I had quite a few fields. It seems to work great and no confused data 🙂

[#9]

Thank you all for your replies and suggestions!