Hello,
I have two sets of large data. Below are simplified examples:
File 1:
Type | Code | Description | Box 1 | Box 2 | Box 3 |
Earning | A | AAA | 11 | 22 | 33 |
Earning | B | BBB | 11 | 22 | 33 |
Earning | C | CCC | 11 | 33 | |
Deduction | D | DDD | |||
Deduction | E | EEE |
File 2:
Description | Box - Other1 | Box - Other2 |
CCC | 40 | |
DDD | 50 | |
EEE | 60 |
I want to combine them into below:
Type | Code | Description | Box 1 | Box 2 | Box 3 | Box - Other1 | Box - Other2 |
Earning | A | AAA | 11 | 22 | 33 | ||
Earning | B | BBB | 11 | 22 | 33 | ||
Earning | C | CCC | 11 | 33 | 40 | ||
Deduction | D | DDD | 50 | ||||
Deduction | E | EEE | 60 |
I uses Joint by Description first and then Summarize by Description, but Box - Other1 and Box - Other1 only returned Null. Should I do it differently?
Thanks!
¡Resuelto! Ir a solución.
Hi @gc-804
Since your File 1 has all the records in the correct order that you want your output in, let's take advantage of that by assigning a Record ID for later. Now we can Join the files on the [Description] field, union the joined and un-joined results and sort to the original order with the Record ID. Then we can use a Data Cleansing tool takes care of the empty and null values in our output. At the end of this workflow, you could use a Select tool to remove the {Record ID] field that was added at the beginning, but I left it in there for now as an example.
Check out the attached workflow for an example of this in action.