Hey community,
Yet again I'm unable to find resolution for a table manipulation that I'm working with.
I've been trying outer join with 2 different tables (1 with correct values, and second one that needs to be fixed) However, Ive been unable to figure how to solve this properly.
The use case is described below and after that there are some notes what I have tried.
After few transformations my table looks like this (snippet of the beginning):
| Name | Value |
| 0 | 38 |
| 1 | 111 |
| 3 | 149 |
| 5 | 799 |
| 6 | 4636 |
| 7 | 5435 |
The table continues as such, total amount of rows is a bit over 3000.
However the issue in this is that the data structure is strictly based on "index" (Name) here and it's missing the null values completely which I need in order to post process the data in a more profound manner.
The target outcome should be following from the source table
| Name | Value |
| 0 | 38 |
| 1 | 111 |
| 2 | 0 |
| 3 | 149 |
| 4 | 0 |
| 5 | 799 |
| 6 | 4636 |
| 7 | 5435 |
So based on the Name column, whenever its "skipping" a number I need to add a row with value being 0.
What have I tried?
I tried to create another table which has the 3000 rows as index values and then outer join to this source table I got following result...
| Index | Name | Value |
| 0 | 0 | 38 |
| 1 | 1 | 111 |
| 2 | 3 | 149 |
| 3 | 5 | 799 |
| 4 | 6 | 4636 |
| 5 | 7 | 5435 |
| ... | ... | ... |
| 2630 | null | null |
Which is closer to the desired result, however what I struggle here is that how can I make the table to actually follow the (add missing spots) the index column?
i.e so it would be something like this
| Index | Name | Value |
| 0 | 0 | 38 |
| 1 | 1 | 111 |
| 2 | null | null |
| 3 | 3 | 149 |
| 4 | null | null |
| 5 | 5 | 799 |
| ... | ... | ... |
I have a feeling that I'm trying to over engineer this and hitting a wall here.
Is there anyone in the community that could assist with the above case?
