Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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....
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;
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,
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
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;
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 🙂
Thank you all for your replies and suggestions!