I am trying to uncollapse a column based on a certain action; however the action can have multiple values.
Sensor ID | Batch ID | Bizstep | Location | Customer | Contact |
IT-112779 | 53801991 | 1 | Miami | Matches acct ID | Matches acct ID |
IT-112779 | 53802071 | 2 | Miami | Matches acct ID | Matches acct ID |
IT-112779 | 53866818 | 2 | New York | Matches acct ID | Matches acct ID |
IT-112779 | 53866830 | 3 | Boston | Matches acct ID | Matches acct ID |
IT-112779 | 53866830 | 3 | NULL | Matches acct ID | Matches acct ID |
- Sensor ID: will is the same within the group of actions (batch)
- Batch ID: is unique to the Bizstep
- Bizstep: indicates the action taken , only 3 actions are possible but actions can be done multiple times by different locations
- Location: Locations can be in different Bizsteps or same location same Bizstep or NULL
- Customer and Contact columns must still show in final dataset.
Below is what I would like to see:
Sensor ID | Action 1 | Action 2 | Action 2 | Action 3 | Action 3 | Customer | Contact |
IT-112779 | Miami | Miami | New York | Boston | Null | Customer | Contact |
Is there any way to do this in ETL? Would prefer to do this in ETL and not in a beast mode as there will also be a beast mode that will look at the data and determine if an action was done by the same location, a different one or not at all.