Request for advice: create new dataset from two sources

Solved!
wjkelly
Level 3
Request for advice: create new dataset from two sources

I imagine this is solved by a join, but I'm having trouble figuring out the steps here. In Excel I would do a vlookup and build the concatenated string from the vlookups and "&" symbols in the formula.

Source 1 is my customer list. The key column is "Customer ID". A simplified structure is:

 

Field/Example:

Customer ID/050-2384756ADB7

Brand tags/Brand1 - Status, Brand2 - Status, Brand3 - Status

 

Source 2 is a pivot table I built from a source of purchase data

 

Field/Example:

Customer ID/050-2384756ADB7

Brand1 Status: Activated/In-Activation/Needs Reactivation/Sampled

Brand2 Status: Activated/In-Activation/Needs Reactivation/Sampled

Brand3 Status: Activated/In-Activation/Needs Reactivation/Sampled

 

So, for each Customer ID, there will be anywhere from 0 to 10 brand status, and I'd like to gather up all the brand statuses by Customer ID and concatenate them into a single field (the "Brand tags" field in the Customer List.

 

Any ideas would be much appreciated.


Operating system used: MacOS Monterey v12.4

0 Kudos
1 Solution
ktgross15
Dataiker

Hi @wjkelly ,

Yes, you can use a join recipe to do this - this knowledge base article should be helpful. You may also want to check out our article on Excel to DSS.

But basically, you'd set up a join recipe, joining source 1 with source 2 via the customer ID columns.

If you then wanted to concatenate them into a single column, you could set up a prepare recipe with a concatenate columns step.

Katie

View solution in original post

0 Kudos
2 Replies
ktgross15
Dataiker

Hi @wjkelly ,

Yes, you can use a join recipe to do this - this knowledge base article should be helpful. You may also want to check out our article on Excel to DSS.

But basically, you'd set up a join recipe, joining source 1 with source 2 via the customer ID columns.

If you then wanted to concatenate them into a single column, you could set up a prepare recipe with a concatenate columns step.

Katie

0 Kudos
wjkelly
Level 3
Author

Thanks Katie -- that method works great. I need to embrace the notion of multiple steps in a recipe rather than trying to solve everything like I do in Excel. 

0 Kudos