I have 2 tables, the first one is the data table that I get daily from a source and the second one is a static table where there are parameter information for every 15 minutes.
The problem is, as you can see in the first table, I didn't get Value1 or Value2 for 00:15, 00:30 from the source. I want to insert the values with matching all parameters from table1 to table2. And if there is a mismatch, I want it to insert 0.
| Parameter1 | Parameter2 | Parameter3 | Parameter4 | Value1 | Value2 |
|---|---|---|---|---|---|
| 00:00 | 1434 | A10 | B10 | 1 | 1 |
| 00:45 | 1434 | A10 | B10 | 2 | 2 |
| 01:00 | 1434 | A10 | B10 | 3 | 3 |
| 01:15 | 1434 | A10 | B10 | 4 | 4 |
| Parameter1 | Parameter2 | Parameter3 | Parameter4 | Value1 | Value2 |
|---|---|---|---|---|---|
| 00:00 | 1434 | A10 | B10 | ||
| 00:15 | 1434 | A10 | B10 | ||
| 00:30 | 1434 | A10 | B10 | ||
| 00:45 | 1434 | A10 | B10 | ||
| 01:00 | 1434 | A10 | B10 | ||
| 01:15 | 1434 | A10 | B10 | ||
| 00:00 | 1434 | A11 | B11 | ||
| 00:15 | 1434 | A11 | B11 |
The final table should look like this.
| Parameter1 | Parameter2 | Parameter3 | Parameter4 | Value1 | Value2 |
|---|---|---|---|---|---|
| 00:00 | 1434 | A10 | B10 | 1 | 1 |
| 00:15 | 1434 | A10 | B10 | 0 | 0 |
| 00:30 | 1434 | A10 | B10 | 0 | 0 |
| 00:45 | 1434 | A10 | B10 | 2 | 2 |
| 01:00 | 1434 | A10 | B10 | 3 | 3 |
| 01:15 | 1434 | A10 | B10 | 4 | 4 |
| 00:00 | 1434 | A11 | B11 | 0 | 0 |
| 00:15 | 1434 | A11 | B11 | 0 | 0 |
I tried to use =VLOOKUP function but I couldn't figure out how I was supposed to use it having multiple parameters and values.
The reason behind I'm trying to this on Excel is because I don't want to do all these process one by one on SQL. Here's how I do all these things on SQL one by one.
- I import the raw data to SQL table.
- I import the parameter values to another SQL table. (I import table2 so Value1 and Value2 come as NULL)
- Then I update the parameter value table.
- Now that I have the table I want, I simply change all the NULL values with 0. So that is an another step :)
Here is the update code for step 3:
UPDATE a SET a.Value1 = b.Value1, a.Value2 = b.Value2,
FROM Table2 a
INNER JOIN Table1 b ON a.Parameter1 = b.Parameter1 AND a.Parameter2 = b.Parameter2 AND a.Parameter3 = b.Parameter3
CodePudding user response：
You can try FILTER() formula with. BYROW() for dynamically iterate each row and spill results.
=BYROW(H2:K7,LAMBDA(x,FILTER(E2:E5,A2:A5&B2:B5&C2:C5&D2:D5=CONCAT(x),0)))
=BYROW(H2:K7,LAMBDA(x,FILTER(F2:F5,A2:A5&B2:B5&C2:C5&D2:D5=CONCAT(x),0)))
