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
Best Answer
-
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
Answers
-
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.