Home > Software engineering >  Find Column Groups in Data
Find Column Groups in Data

Time:10-11

I have an excel file with Data of users who visited specific screens on an App. eg. enter image description here

I need to figure out a way to find multiple screen group users

eg.

enter image description here

The number of unique screens are 6 so manually making this list isn't scalable with all permutations and combinations.

Tools available are excel and python and sql.

CodePudding user response:

You not provided your RDBMS version, but in common case you can aggregate activity types by user_id. Below query for MySQL:

SELECT user_id, GROUP_CONCAT(DISTINCT activity_type ORDER BY activity_type ASC) activities
FROM log
GROUP BY user_id
order by activities;

enter image description here

Formula in E2:

=UNIQUE(FILTER(Tabel1[ID],Tabel1[Screen]=D2))

Formula in H2:

=UNIQUE(FILTER(Tabel1[ID],BYROW(COUNTIFS(Table1[ID],Table1[ID],Table1[Screen],TOROW(G2:G3)),LAMBDA(a,PRODUCT(a))),""))
  • Related