Request for advice: create new dataset from two sources

wjkelly
wjkelly Registered Posts: 22 ✭✭✭✭

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

Tagged:

Best Answer

Answers

  • wjkelly
    wjkelly Registered Posts: 22 ✭✭✭✭

    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.

Setup Info
    Tags
      Help me…