Need help in extracting similar names in Lookup response and also get the count of times repeated

Ask for Help
1

Hi Guys,

I have a query which gives me response for number of campaigns for given brand.

I find lookup and get creator names

Screenshot 2024-08-08 at 1.51.02 PM

I want to get a response as follows

Gopika Ramesh ---- 2
Pradini SUrva ----1

Meaning it should find all lookup values and find which repeats top 3 times and give me the response.

How can I get this data from Lookup.

Thank you in advance for help.

Regards,
Dilip

2

Can I use some Java script to do this?

Regards,
Dilip

3

Used this script

function findMostCommonValues(list) {
let countMap = list.split(‘,’)
.reduce((acc, curr) => ((acc[curr] = (acc[curr] || 0) + 1), acc), {});

let highestCount = Math.max(…Object.values(countMap));
return Object.keys(countMap).filter(key => countMap[key] === highestCount);
}

let list = p1;
let mostCommonValues = findMostCommonValues(list);
return mostCommonValues.toString();

Have configured it like this on my New Column B which has values separated by ,

Screenshot 2024-08-08 at 2.33.09 PM
Screenshot 2024-08-08 at 2.33.09 PM888×798 138 KB

Can someone let me know if there is any issue in my JS?

Regards,
Dilip

4

Let me know if someone has found solution to this issue.

Its a show stopper for one of my feature.

Thank you in advance
Regards,
Dilip

5

So what exact output do you want? Can you give us an example from your input data above?

6

Hi @ThinhDinh

I would want Javascript to Analyse all the Values in the column

and tell me

Gopika Ramesh is selected 2 times
Pradaini Surva is selected 1 time

Basically show top 3 repeated values.

Regards,
Dilip

7

If I’m understanding this correctly, then JavaScript is overkill for this. Let me ask a question - do you have a list of unique names in some other table?

1 Like
8

One further question here, why do you seem to have the same list on every line? What’s the ultimate use case of this?

image
image1820×562 91.6 KB

image
image1816×1620 160 KB

If you still want to use JS, here it is:

function getTopThreeNames(input) {
  const names = input.split(', ');
  
  const nameCounts = {};
  names.forEach(name => {
    nameCounts[name] = (nameCounts[name] || 0) + 1;
  });
  
  const sortedNames = Object.entries(nameCounts)
    .sort((a, b) => b[1] - a[1] || a[0].localeCompare(b[0]));
  
  const result = [];
  let currentRank = 1;
  let currentCount = sortedNames[0][1];
  
  for (const [name, count] of sortedNames) {
    if (count !== currentCount) {
      currentRank++;
      currentCount = count;
    }
    
    if (currentRank <= 3) {
      result.push(`${name} is selected ${count} time${count !== 1 ? 's' : ''}`);
    } else if (currentRank === 4 && result.length < 3) {
      result.push(`${name} is selected ${count} time${count !== 1 ? 's' : ''}`);
    } else {
      break;
    }
  }
  
  return result.join('\n');
}

return getTopThreeNames(p1)
1 Like
9

Hi @Darren_Murphy ,

Yes I have list of all the Unique names in some table separately.

Regards,
Dilip

10

That’s because its a response from a query that I am getting which I am using.

Will try the script now.

Regards,
Dilip

11

Okay, so what I would do is as follows:

  • Create a multiple relation column in that table that matches the name with the name in your Responses table
  • Use a rollup to get a count of responses via the relation
  • Use that same table as the source of a Collection
  • Order the collection by the count of responses in descending order
  • Limit it to 3 rows
12

Hi @Darren_Murphy ,

Thank you for responding.

How can I do it? Basically my Input can have Creator 1 2 times , Creator 2 3 times and Creator 3 1 time.

How would this have to be done?

Regards,
Dilip

13

Use a Split Text column to coerce that into an array, and then match that with the relation.

1 Like
14

Hi @Darren_Murphy ,

Thank you for replying. Sorry wasn’t working for few days as I was unwell.

I did this and get the relation, but how can I induce it to give me text like

Creator1 was selected 2 times etc etc in descending order

Specially since names will be different.

Regards,
Dilip

15

Can you show me a screen shot of what you have so far in the data editor?
I’m most interested in the table that has the list of unique names.

1 Like
16

Hi @Darren_Murphy

I have input my query response to rollup column where I am matching the response of my query which is

Screenshot 2024-08-14 at 10.10.28 AM

From this I am sending it to Lookup column to get creator names

Which is as Below

Screenshot 2024-08-14 at 10.13.24 AM

To this I am applying relation column where it checks the name and compares it with the List where I am storing these creators

Screenshot 2024-08-14 at 10.14.17 AM

But here instead of 3 I am getting only 2 as one of the names are common

I am getting stuck after this.

I split the text from my lookup column and in my editor I can display it like this

Screenshot 2024-08-14 at 10.16.26 AM

Hope I didnt confuse you.

Thank you for responding.

Regards,
Dilip

17

Dilip - is this accurate?

You have X number of Creators and Y number of Campaigns and Z number of Brands. Brands can have multiple Campaigns. Creators work on multiple Campaigns across multiple Brands. Campaigns are associated with ONE Brand but potentially Multiple Creators.

You want to list the top 3 Creators and the # of Campaigns they worked on across all Brands- correct?

18

Hi @MattLB ,

Thank you for responding.

Basically I have X number of Creators, Who have worked on Y number of campaigns.

And Z number of agency is associated with these campaigns.

Yes one brand is associated with multiple creators.

I want to list the Top 3 creators who worked with selected Agency.

Regards,
Dilip