Skip to main content

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.
 

UPDATE

I tried using Representative Creator. Here is the cofiguration :

Group By MASTER_ID
Representative columns
Sys1_id =SOURCE_ID if SOURCE_SYSTEM=’Sys1’ and so on for other systems.
This works but misses the edge case mentioned above. So, if a master record is a result of matches from different systems and each system does not have a duplicate within itself for a particular record, the above configuration works. Can someone please help with the edge case mentioned above?

Thanks in advance

Reply


Community Terms of Use & ConditionsCookie settings