Zip key pairs in query?

[dobsi]

What I’m trying to do

My notes contain the keys: date_A, text_A, date_B, text_B.
In a query I would like to aggregate all dates (both from date_A & date_B) in a single column called date if they are within a given year (this part is solved thanks to Aggregate different keys into one column and sort by this column).
In a column called text I would then like to display the corresponing texts. So if in a certain row the date column entry is coming from date_A, I would like to display text_A in the text column and so on.

Things I have tried

I have the following query:

```dataview
TABLE
date_A,
date_B,
text_A,
text_B,
date,
text

FROM "Testing"
FLATTEN list(date_A, date_B) as date
FLATTEN list(text_A, text_B) as text

WHERE date.year=2023

This, however, due to the two FLATTEN commands includes too many entries, as also “wrong” combinations (indicated by the red strikethrough) are shown. Is there a way to somehow zip the lists or another way to achieve the desired result?

[holroy]

You’re mixing various queries here, which might be, but could also not be, a good solution. Please show us some of your original data from Note 1. I’m thinking that possibly you’re better of with changing your syntax instead of trying to zip together possibly unrelated data (as seen from dataview’s context).

[dobsi]

So for this MWE the data in Note 1 is really just what is shown in the picture, i.e.:

---
date_A: 2023-01-01
text_A: "N1 Text A"
date_B: 2023-01-02
text_B: "N1 Text B"
---

For my actual usecase I’m not sure if I can still easily change the syntax. It is supposed to capture the deadlines and titles of different reports.

[holroy]

This would be better represented doing something like:

---
reports:
- name: "A"
  date: 2023-01-01
  text: "N1 Text A!"
- name: "B"
  date: 2023-01-2
  text: "N1 Text B"
---

Using a proper structure like this would enable your queries to be a lot more logical. Instead of trying to link entities which are not properly defined as part of the same structure.

In your example there is nothing connecting the various fields together, and to write a query on top of that is going to cause you a lot of headache.

[dobsi]

Ok, thanks a lot. I see that this is probably a way better structure for the data and I will use this in the future.
However, for my current vault I can’t change it easily due to some further restrictions. But I guess at least using dataviewjs it should somehow be possible to achieve the desired result even with the current form of data?

[holroy]

You should be able to retrospectively build those structures, but it’s not very nice… If your use case just to get a date/text combination from one of the field combos, you could do something like:

```dataview
TABLE  a.date, a.text
FROM "Testing"
FLATTEN choice(date_A.year = 2023, 
  object("date", date_A, "text", text_A),
  object("date", date_B, "text", text_B)) as a 
```

This would construct a new object with date and text, and is shown with a default attitude of selecting “B” if “A” is not in the year 2023. The choice() could be further extended if your test case for choosing either entry is more advanced.

If however you’ll want to extract either zero, one or two of the entries having the year 2023, you’ll need a different approach, possibly something like the following thingy:

```dataview
TABLE a.date, a.text
FROM "Testing"
FLATTEN list(
  object("date", date_A, "text", text_A),
  object("date", date_B, "text", text_B)) as a
WHERE a.date.year = 2023
```

Here we construct a list of the constructed objects, and then flatten that list to extract only the wanted combination of entries.

My test set

To test the previous query I’ve got the following test set:

And the final output using the query:

The method depicted above of constructing a list of objects can be extended to whatever level you want with regards of how many elements to have in the object, and how many variations you want “A”, “B”, … It just gets very repetetive and boring to write… And it is a very static structure, as opposed to the dynamic structure depicted in an earlier post.

---

See also choice() & object() for more information on these two functions to be used in a standard query.

[dobsi]

This works great! Thanks a lot for this detailed answer. The object function was what I was looking for!