I want to convert the instance export table values to a new table/view.
The instance export contains 3 columns that I want to utilise
Sharing an example-
I have the following instance table structure :
| MASTER_ID | SOURCE_SYSTEM | SOURCE_ID |
|---|---|---|
| 001 | sys1 | 1234 |
| 001 | sys2 | 12232 |
| 001 | sys3 | 1292 |
| 001 | sys4 | 13432 |
| 001 | sys2 | 12334 |
5 records from 4 different source system make a master record, I want to group it in such a manner that there is a single row for each source_system id. If a source system participate twice(with two unique records) in formation of master record, I want two rows representing the different source_ids and a column with the source system that participates twice. Here is how I intend the output table to be:
| MASTER_ID | SYS1_ID | SYS2_ID | SYS3_ID | SYS4_ID | MULTIPLE_REC |
|---|---|---|---|---|---|
| 001 | 1234 | 12232 | 1292 | 13432 | SYS2 |
| 001 | 1234 | 12234 | 1292 | 13432 | SYS2 |
Can someone help me with the ONE Desktop step I can use for this?
I have explored using Aggregating Column Assigner to group records but I but that wouldnt help.
I think I can use representative creator but not sure of the configurations.