Question

Join Rows based on account ID

  • 22 March 2023
  • 2 replies
  • 48 views

Badge +3

Hi Team,
I have a scenario where I need to join rows based on account ID

Here is the dataset:

Account ID         Site_name  

1                         Google

1                          Yahoo

2                          Linkedin

2                          Indeed

2                          Gmail

 

Output expected:

Acccount ID           Sitenames
1                             Google, Yahoo

2                              Linkedin, Indeed, Gmail

Has anyone experienced this use case.
Looking forward to hear some solutions


2 replies

Userlevel 6
Badge +9

Hi @Harika Singadi 

Off the top of my head, I’d say you need to (in data designer):

  1. Fetch task - all accounts with IDs in one task as your list of active accounts (no need for sitename)
  2. Fetch tasks - account ID with entries by site name (i.e., filter on site name = or contains Google) and repeat for each of the sites - it works if you have a predefined list - if it can be literally anything, then this will be messy/not really working
  3. Merge left task 1, with task 2 (and all subsequent tasks, making sure you always merge left)
  4. Transform task - add a formula field to concatenate into one new field

There might be a simpler way but it doesn’t come to me.

Depending on the actual use case, a pivot task could do something different but still functional to understand if an account is using site A or B (with 0 and 1 values (or more if it’s counting logins).

Hope this helps! 

Badge +3

Hi @alizee ,
Thanks for  the quick reply.
Unfortunately each client uses different site names as well there is no predefined list .

 

 

Thanks,

Harika

Reply