For something like that to work you need to focus on each list item as a separate entity, and in your case the list item I would focus especially on are the list items containing the consumed field. So here is a query focusing on those particular items, and doing some additional magic:
```dataview
TABLE WITHOUT ID P.day, item.consumed, item.line, item.parent, item.children.parent, item.children.text, item.children.impression, P.day
WHERE file = this.file // Replace with something picking your files
FLATTEN file.lists as item
WHERE item.consumed
FLATTEN filter(file.lists, (fl) => fl.line = item.parent) as P
```
I’m just working with the current file here for ease of reference, but it should work for a broader set of files as well. The first order of business is to split all the list items in the file into separate items, FLATTEN file.lists as item. I then narrow down to just look at the items with the consumed field, in the WHERE item.consumed line.
Now what is consumed is easily accessible through item.consumed, but how to get the parent and the children? Well, the item has references to the line number of it self and its parent. These are displayed as item.parent and item.line in the query above. So to get access to the parent item of this particular consumed item, we can filter on the original list of file.lists where its line number matches our items parent number. This is the last line of the query, and it stores the parent item into P so that we can access and look up which day our current item belongs to.
In the last few columns, I display some information related to the children, and since in your case it seems like every children of a consumed item has just the impression, we can use the item.children.impression directly to lift out those values. We could also have made something similar to how we picked the P and switch around the parent and line expression, or even in some cases do filter directly on the item.children.
Note though that if you want to stray more than one level away from your current item you’re asking for problems as the expressions gets really hairy.
Q&A time
1. impression doesn’t return as expected
As you’ve written the query you’re referencing the impression in the page or note context, not from within the list item you’re looking at. This means it’ll produce every impression it can find in the entire file.
Flatten on ListItem.consumed.impression
Here you need to take it step by step. The combination of ListItem.consumed doesn’t return the item of the list, just the value of consumed from within ListItem. As such you can’t dive further into that value to find any impressions
flatten list(impression)
This essentially returns the list of impressions and yet again we’re in the page context, so naturally it returns all impressions. There’s nothing limiting it to any specific consumed item.
flatten day returns inifinte number of days
In the start there is one file per row in your table. After the FLATTEN day you’ve got one row for each day in each file. After the next FLATTEN file.lists as ListItems you multiply each of those days with however many list items you’ve got in any given file. The last FLATTEN ListItems as ListItem I don’t think do anything useful.
Then you limit down the number of rows related to whether it has a consumed, so you’re actually quite close to my query above, but the damage of multiplying the file by days has already occurred so each ListItem.consumed is duplicated by the number of days in that particular file.