How to uncollapse column when group has multiple values

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.

Find more posts tagged with

Comments

PodiumMason

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.

vcsmedia

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

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

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.