What I’m trying to do
I am collecting research notes on music bands and have a note for each band which contains list properties for members (current members) and members-past (former members).
I am trying to create a dataview which shows each musician and the bands that they have belonged to like this…
Tommy Bolin - The James Gang
Deep Purple
Glenn Hughes - Trapeze
Deep Purple
The musician can be in the members or members-past property so these need to be merged.
Things I have tried
The below works well but does not merge the musician properties and only outputs from the members property
TABLE rows.artist as Artist
FROM "Music/Artist"
flatten members
sort members asc
Group by members
In the below I am merging the two members & members-past properties but they come out as one list with the artist as a separate list in the next column i.e. The musician does not line up against the artist they belonged to.
TABLE without id filter(split((rows.members + "," + rows.members-past), ","), (x) => x != "-") as "musician", rows.artist as Artist
FROM "Music/Artist"
FLATTEN musicican
sort musician asc
Group by musician
Any help on how to improve this wodul be appreciated.