Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I've got the following data (ProjectName is key):
Table1:
ProjectName
Projectmanager_pr
Days_pr
Table2:
ProjectName
Projectmanager_ta
Days_ta
Now I wist to have a list showing all unique values from Projectmanager_pr & Projectmanager_ta. This so I can have a list of all the employees no matter if they are a Projectmanager_pr or Projectmanager_ta, then I can filter on their names and get their data no matter in what table they are listed.
Sample data:
Table 1
ProjectName | Projectmanager_pr | Days_pr |
---|---|---|
Project1 | Anna | 5 |
Project2 | Erik | 10 |
Project3 | Stefan | 15 |
Table 2
ProjectName | Projectmanager_ta | Days_ta |
---|---|---|
Project1 | Anna | 20 |
Project2 | Anna | 30 |
Project3 | Lars | 40 |
Outcome
Employees |
---|
Anna |
Erik |
Stefan |
Lars |
Selecting for example Anna above I would get the below results in another object:
ProjectName | Days_pr | Days_ta |
---|---|---|
Project1 | 5 | 20 |
Project2 | 30 |
Thanks in advance
I managed to get it right by doing like this:
Table1:
LOAD
ProjectName AS ProjectName,
Projectmanager_pr AS Projectmanager_pr,
Days_pr AS Days_pr
FROM X;
Table2:
LOAD
ProjectName AS ProjectName,
Projectmanager_ta AS Projectmanager_ta,
Days_ta AS Days_ta
FROM X;
Combinedtable:
LOAD
ProjectName AS ProjectName,
Projectmanager_pr AS ProjectManager
RESIDENT Table1;
LOAD
ProjectName AS ProjectName
Projectmanager_ta AS ProjectManager
RESIDENT Table2;
Concatenate the two tables in the script so you have only one table:
Table1:
load
ProjectName,
Projectmanager_pr as Projectmanager,
Days_pr as Days,
'Pr' as Type
from table1source;
concatenate(Table1)
load
ProjectName,
Projectmanager_ta as Projectmanager,
Days_ta as Days,
'Ta' as Type
from table2source;
Then in your table you can calculate the days for the different Types with expressions like this
Days_pr: sum({<Type={'Pr'}>} Days)
Days_ta: sum({<Type={'Ta'}>} Days)
Thank you both for your answers! This was a simplified example, in reality there are 50 fields per table.
Should I really join these big tables when I only want to join one field from each table?
Will my key stay the same still?
I just want a list of all the employees, they shouldn't be used as key.
Sorry, I am new to QV, hope I don't use the wrong terminology.
I managed to get it right by doing like this:
Table1:
LOAD
ProjectName AS ProjectName,
Projectmanager_pr AS Projectmanager_pr,
Days_pr AS Days_pr
FROM X;
Table2:
LOAD
ProjectName AS ProjectName,
Projectmanager_ta AS Projectmanager_ta,
Days_ta AS Days_ta
FROM X;
Combinedtable:
LOAD
ProjectName AS ProjectName,
Projectmanager_pr AS ProjectManager
RESIDENT Table1;
LOAD
ProjectName AS ProjectName
Projectmanager_ta AS ProjectManager
RESIDENT Table2;