Dataview, group by not working

I have some CRM type functionality in my notes. I take meeting notes, and have links to all the people pages for those at the meeting. A persons page has a front matter attribute called “company” so I know who works where. Then I have a company page, that describes that company, uses a dataview query to pull in a table of everyone who works there that I have had meetings with.

The tragedy occurs when I want a table below that, which is a list of all of the meetings I have had involving that company.

I have the following query,

Table without id dateformat(meeting_date, "dd-MM-yyyy") as "Date", As Title

FROM "Calendar/Meetings"
FLATTEN file.outlinks as People
WHERE = "Microsoft"

That works as expected and gives me a table of data. If I have multiple people from Microsoft in a single meeting, then I get one line for each of them. It makes complete sense. So a meeting with four microsoft people, gives me four entries.

So clearly the answer is a GROUP BY… except it doesn’t work…

Adding the single line
to the end of my query, gives me a table with one entry, but the values are just - signs.

i have tried calling it instead, I have tried calling it… I have tried calling it rows.Title… nothing changes the output. It just doesn’t show any data

I am genuinely perplexed… everything is the latest version…

All ideas welcomed, thanks in advance.

Given a simple query like TABLE a, b GROUP BY a, will group all results into rows for each unique value of a which is also duplicated into key. Due to this grouping, the above query would display the - for empty results, since now you’ll find the grouped together results in rows.a and rows.b and so on.

In your particular case you also change the dateformat when display a value, and that can’t just be a simple addition of rows. in front of the variable. So I’d suggest using FLATTEN ... as Date before the GROUP BY statement, and then doing rows.Date afterwards.

So the following should start producing some results for you:

TABLE rows.Date as Date, rows.Title as Title
FROM "Calendar/Meetings"
FLATTEN file.outlinks as People
WHERE = "Microsoft"
FLATTEN dateformat(meeting_date, "dd-MM-yyyy") as Date

Also notice that this will make all the single entries in a group be presented as a list, even though there is just one meeting for that person.

Thank you so much for always helping. I even think I understand… at least the flatten bit for date I understand. I now have a table of lists, so the group by is “grouping” them together, but not “unique” ing them. There are four entries for a meeting with four people, and so forth… so I dont understand that bit… Because I haven’t tried to display the People column of the table, but it has “rendered” it invisibly by default when I did the group…

I have even tried to normalise the People, as it’s clearly the problem. I tried
FLATTEN (replace(string(People), “*”, " ")) As People

Thinking if I made everyones name the same, then the group by would have to work as I want, but no luck there either…

For the bonus points, is there are qay to examine the internals to see what it is making at each step?

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.