Forum Discussion
Help calculating value from different tables with multiple criteria
Hi, I'm having trouble adding a colum that will correctly return the values between these two tables. Effectivly, i need to create a column in table 1, that adds up the values of the TotalTimeHours in table 2. BUT, it must only add the values were both nkey and skey1 both match. (there are thousands of differnt nkey's in the table, and many will have multiple skey1.
Table 1
| source_id | nkey | skey1 | skey2 | skey3 | Table_Key | host_id |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 1 | 1 | 1 | 6424266 | 72439306 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 2 | 1 | 1 | 6424265 | 72439306 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 3 | 1 | 1 | 6424264 | 72439306 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 4 | 1 | 1 | 6424263 | 72439306 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 5 | 1 | 1 | 6424262 | 72439306 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 6 | 1 | 1 | 6424261 | 72439306 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 7 | 1 | 1 | 6424260 | 72439306 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 8 | 1 | 1 | 6424256 | 72439306 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 9 | 1 | 1 | 6424259 | 72439306 |
Table 2
| source_id | nkey | skey1 | Table_Key | host_id | database_id | TotalTimeHours |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 1 | 6424292 | 72439306 | STORE412 | 0.05 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 2 | 6424295 | 72439306 | STORE412 | 2.16 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 2 | 6424294 | 72439306 | STORE412 | 0.84 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 4 | 6424293 | 72439306 | STORE412 | 0.04 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 5 | 6424291 | 72439306 | STORE412 | 0.05 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 5 | 6424289 | 72439306 | STORE412 | 0.10 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 7 | 6424288 | 72439306 | STORE412 | 0.10 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 7 | 6424287 | 72439306 | STORE412 | 0.50 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 9 | 6424284 | 72439306 | STORE412 | 0.58 |
End result i'm trying to achieve with Table 1, it needs to be able to idenify, that if there is no match for both nkey and skey1 in table 2 then return 0.00, otherwise show a summ of all values in table 2 that match both the nkey and skey.
| source_id | nkey | skey1 | skey2 | skey3 | Table_Key | host_id | TotalTimeHours |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 1 | 1 | 1 | 6424266 | 72439306 | 0.05 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 2 | 1 | 1 | 6424265 | 72439306 | 3.00 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 3 | 1 | 1 | 6424264 | 72439306 | 0.00 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 4 | 1 | 1 | 6424263 | 72439306 | 0.04 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 5 | 1 | 1 | 6424262 | 72439306 | 0.05 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 6 | 1 | 1 | 6424261 | 72439306 | 0.00 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 7 | 1 | 1 | 6424260 | 72439306 | 0.60 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 8 | 1 | 1 | 6424256 | 72439306 | 0.00 |
| F7F8C66A4ABB44EAC8D145C1DA353FCE | M312538 | 9 | 1 | 1 | 6424259 | 72439306 | 0.58 |
NewStep=let a=Table.Buffer(Table.Group(Table2,{"source_id","nkey","skey1"},{"amt",each List.Sum([TotalTimeHours])})) in Table.AddColumn(Table1,"TotalTimeHours",each a{[source_id=[source_id],nkey=[nkey],skey1=[skey1]]}?[amt]? ??0)
2 Replies
NewStep=let a=Table.Buffer(Table.Group(Table2,{"source_id","nkey","skey1"},{"amt",each List.Sum([TotalTimeHours])})) in Table.AddColumn(Table1,"TotalTimeHours",each a{[source_id=[source_id],nkey=[nkey],skey1=[skey1]]}?[amt]? ??0)