What I’m trying to do
I am trying to remove the duplicated returned values shown in the image below
I initially collect the persons name and sort it alongside their “partneredWith” connection to get a “Name” value that should be duplicated twice
Things I have tried
I have tried using “GROUP BY Name” however this just returns blanks across the row as shown below
I understand that when grouping rows, Dataview needs to aggregate other columns but I’ve tried using min() on marriage date and anniversary but nothing has seemed to work
I fixed the problem by updating the query to the below syntax:
TABLE WITHOUT ID
rows.FlattenedNames[0] As "Names",
rows.marriageDate[0] As "Marriage Date", rows.AnniversaryCalc[0] AS "Anniversary"
FROM #person
WHERE marriageDate.month = 03 AND marriageDate.day = 11
FLATTEN join(sort(list(row.file.link,partneredWith)), ", ") AS "FlattenedNames"
FLATTEN truncate(string(date(today) - marriageDate),2, "") AS "AnniversaryCalc"
GROUP BY FlattenedNames
This now correctly only displays one anniversary per couple.
I was massively helped by reading this excellent thread which really helped explain flatten and group by: "Group by" Woes
