How to uncollapse column when group has multiple values

vcsmedia
vcsmedia Member
in Archive

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.

Comments

  • PodiumMason
    PodiumMason Contributor

    hey @vcsmedia,

     

    This should be possible in ETL. The knowledge base has a good article outlining setting up the "Uncollapse Columns" transform: https://knowledge.domo.com?cid=etlactionseditcolumns

     

    Let me know if you have additional questions.

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • vcsmedia
    vcsmedia Member

    Thank you. I tried this already.  problem is that as example: Product A can happen multiple times on the same day.  Uncollapse function will do 1 of 2 things (depending on the identifier) it will either select the first instance and delete the subsequent instance or will create multiple rows of the same data.  I'm trying to avoid either of these. 

  • ST_Superman
    ST_Superman Domo Employee

    I'll give this a shot in ETL, but you may need to use MySQL to make it work.  You will also need to change your field names.  In the example you gave, you had "Action 2" and "Action 3" both listed twice.  You could either have "Action 2" and concat the locations to give you "Miami | New York" or you could have "Action 2" and "Action 2a".  I also noticed that you are dropping the "Batch ID" is that not needed in the output?

     

    Thanks,

  • vcsmedia
    vcsmedia Member

    Hello Scott,

    Thank you, hope you can provide a solution.  I can't concate the data as I need to identify each action as a separate entry, and the "actions" are system set to the values and can't be changed or added to. So I'm kind of stuck with what I have. 

Categories