Hi everyone. I have loaded this input data:
Users:
Name City
Tom Toronto
Tom New York
Nick Philadelphia
Nick Los Angeles
Nick Philadelphia
Sam New York
Sam Chicago
Sam New York
Sam Chicago
John Phoenix
Vincent San Antonio
Vincent San Antonio
Bob Miami
Bob Boston
I want to reload these data in another table, aggregating by Name field, keeping only the record with most City occurrences, and otherwise, in case of the same number of occurrences for both Cities, to keep the distinct records, without repetition. I show you the result I expect:
Name City
Tom Toronto
Tom New York
Nick Philadelphia
Sam New York
Sam Chicago
John Phoenix
Vincent San Antonio
Bob Miami
Bob Boston
What is the code to achieve this result? I thought something like this:
Load Name,
if(not isnull(mode(City)) -> mode(City)
if(isnull(mode(City)) -> all the distinct records
Resident Users
Group by Name;
Any suggestions?
