I have one table with repeated data which need to be categroized accordingly

Here in this image, we can see there is a table of a database of a number of customers who have attended events in our company, but events were conducted separately so we have data separate with the name of the event by the side.

The only common item is their phone number.

So what I need is: Sam has attended three events it has three rows with the names in it, compile it in a new column called consolidated with a code E1E2E3 so now instead of three rows there is one row with this code

This code helps us understand sam has attended these three events.

Kindly please assist me in providing the formula,

Hi @Rohnak. To consolidate event names in a single column you can use this formula

# Find all records with matching phone number
records = Events.lookupRecords(Phone=$Phone)
# Generate events codes
codes = map(lambda r: "E1" if r.Event1 else "E2" if r.Event2 else "E3" if r.Event3 else "", records)
# Return consolidated code
return "".join(codes)

You can find a working example here Grist .

If your data doesn’t come from an external source, you can try to structure it in a different way, by putting all Attenders, Events and Audience in separate tables.
This will allow you to create more events without affecting table structure and will simplify the formula a bit. I also included an example of this in the linked document.

2 Likes

Thank you so much for the formula jarek, it worked really well amazing :smiley:

But one more problem occurred as there is data is repeated where a phone is unique and attended two same events it started to come as E1E1 but the result i expected is only E1

Here you can see last three rows E2E2E2 but i want it to be only E2

I updated the example with a formula that removes duplicates. I hope that now it works as expected :slight_smile:

records = Events.lookupRecords(Phone=$Phone)
codes = map(lambda r: "E1" if r.Event1 else "E2" if r.Event2 else "E3" if r.Event3 else "", records)
# Here I'm using the set function to convert a list to a set of unique values
# and then a sorted function to sort values in ascending order.
return "".join(sorted(set(codes)))
1 Like

:smiley: Thank you it worked amazing :slight_smile: