Dataview "contains" help

ReaderGuy42 · February 10, 2023, 10:54pm

Ah OK, that makes sense Thank you!!

ReaderGuy42 · February 10, 2023, 11:13pm

Just in case anyone’s interested, I’ve now implemented that into my crazy query:

TABLE without id 
      ("![](" + Cover + ")") AS "Cover",
      P_BookByAuthor + P_MediumIcon + P_FavoriteIcon AS "Title",
      Q_YearRating + Q_PagesHours + " || " + Q_FLAG AS "Time and Place",      
      R_Dates AS "Dates",
      "(" + S_ReadingTime + " || " + S_ReadingSpeed + ")" AS "Reading Time"
      
FROM "Book Log" OR "Y Movies and Games" OR "0 Currently Reading" OR "Set Aside"

FLATTEN {
    "Book": Author,
    "Audiobook": Author,
    "Podcast": Author,
    "eBook": Author,
    "AcademicBook": Author,
    "AcademicArticle": Author,
    "GraphicNovel": Author,
    "Movie": Director,
    "TV": Director,
    "Game": Director
}[Medium] AS OneAuthor
WHERE econtains(meta(OneAuthor).path, this.file.path)
SORT DateFinished asc


FLATTEN link(file.link, Alias) + " by " + OneAuthor AS P_BookByAuthor
FLATTEN choice(contains(tags, "Favorite"), "💛", "") AS P_FavoriteIcon

FLATTEN {
    "Book": " 📖 ",
    "Audiobook": " 🎧 ",
    "Podcast": " 📻 ",
    "eBook": " 📃 ",
    "AcademicBook": " 📚🎓 ",
    "AcademicArticle": " 📰 ",
    "GraphicNovel": " 💬 ",
	"Movie": " 🎞️ ",
	"TV": " 📺 ",
	"Game": " 🎮 "
}[Medium] AS P_MediumIcon



FLATTEN DateStarted + " — " + DateFinished AS R_Dates

FLATTEN choice(((DateFinished - DateStarted).days = 1), "1 day",  choice((DateFinished != DateStarted), (DateFinished - DateStarted).days + " days", "0 days" )) AS S_ReadingTime


FLATTEN {
"Book": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"Audiobook": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 2) + " h/d",
"Podcast": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 2)+ " h/d",
"eBook": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"GraphicNovel": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"AcademicBook": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"AcademicArticle": round(choice((DateFinished = DateStarted), Length, (Length / (DateFinished - DateStarted).days)), 1) + " p/d",
"Movie": round(choice((DateFinished = DateStarted), round((Length/60), 1), (Length / (DateFinished - DateStarted).days)), 1) + " h/d",
"TV": round(choice((DateFinished = DateStarted), round((Length/60), 1), (Length / (DateFinished - DateStarted).days)), 1) + " h/d",
"Game": round(choice((DateFinished = DateStarted), round((Length/60), 1), (Length / (DateFinished - DateStarted).days)), 1) + " h/d"
}[Medium] AS S_ReadingSpeed


FLATTEN Year + " || " + " ⭐ " + Rating + " || " AS Q_YearRating

FLATTEN {
    "Book": Length + " p",
    "Audiobook": Length + " h",
    "Podcast": Length + " h",
    "eBook": Length + " p",
    "GraphicNovel": Length + " p",
    "AcademicBook": Length + " p",
    "AcademicArticle": Length + " p",
	"Movie": round((Length/60), 1) + " h",
	"TV": round((Length/60), 1) + " h",
	"Game": round((Length/60), 1) + " h"
}[Medium] AS Q_PagesHours


FLATTEN join(map(split(Country, ", "), (c) => {
AD: "🇦🇩",
AE: "🇦🇪",
AF: "🇦🇫",
AG: "🇦🇬",
AI: "🇦🇮",
AL: "🇦🇱",
AM: "🇦🇲",
AO: "🇦🇴",
AQ: "🇦🇶",
AR: "🇦🇷",
AS: "🇦🇸",
AT: "🇦🇹",
AU: "🇦🇺",
AW: "🇦🇼",
AX: "🇦🇽",
AZ: "🇦🇿",
BA: "🇧🇦",
BB: "🇧🇧",
BD: "🇧🇩",
BE: "🇧🇪",
BF: "🇧🇫",
BG: "🇧🇬",
BH: "🇧🇭",
BI: "🇧🇮",
BJ: "🇧🇯",
BL: "🇧🇱",
BM: "🇧🇲",
BN: "🇧🇳",
BO: "🇧🇴",
BQ: "🇧🇶",
BR: "🇧🇷",
BS: "🇧🇸",
BT: "🇧🇹",
BV: "🇧🇻",
BW: "🇧🇼",
BY: "🇧🇾",
BZ: "🇧🇿",
CA: "🇨🇦",
CC: "🇨🇨",
CD: "🇨🇩",
CF: "🇨🇫",
CG: "🇨🇬",
CH: "🇨🇭",
CI: "🇨🇮",
CK: "🇨🇰",
CL: "🇨🇱",
CM: "🇨🇲",
CN: "🇨🇳",
CO: "🇨🇴",
CR: "🇨🇷",
CU: "🇨🇺",
CV: "🇨🇻",
CW: "🇨🇼",
CX: "🇨🇽",
CY: "🇨🇾",
CYMRU: "🏴󠁧󠁢󠁷󠁬󠁳󠁿",
CZ: "🇨🇿",
DE: "🇩🇪",
DJ: "🇩🇯",
DK: "🇩🇰",
DM: "🇩🇲",
DO: "🇩🇴",
DZ: "🇩🇿",
EC: "🇪🇨",
EE: "🇪🇪",
EG: "🇪🇬",
EH: "🇪🇭",
ENG: "🏴󠁧󠁢󠁥󠁮󠁧󠁿",
ER: "🇪🇷",
ES: "🇪🇸",
ET: "🇪🇹",
FI: "🇫🇮",
FJ: "🇫🇯",
FK: "🇫🇰",
FM: "🇫🇲",
FO: "🇫🇴",
FR: "🇫🇷",
GA: "🇬🇦",
GB: "🇬🇧",
GD: "🇬🇩",
GE: "🇬🇪",
GF: "🇬🇫",
GG: "🇬🇬",
GH: "🇬🇭",
GI: "🇬🇮",
GL: "🇬🇱",
GM: "🇬🇲",
GN: "🇬🇳",
GP: "🇬🇵",
GQ: "🇬🇶",
GR: "🇬🇷",
GS: "🇬🇸",
GT: "🇬🇹",
GU: "🇬🇺",
GW: "🇬🇼",
GY: "🇬🇾",
HK: "🇭🇰",
HM: "🇭🇲",
HN: "🇭🇳",
HR: "🇭🇷",
HT: "🇭🇹",
HU: "🇭🇺",
ID: "🇮🇩",
IE: "🇮🇪",
IL: "🇮🇱",
IM: "🇮🇲",
IN: "🇮🇳",
IO: "🇮🇴",
IQ: "🇮🇶",
IR: "🇮🇷",
IS: "🇮🇸",
IT: "🇮🇹",
JE: "🇯🇪",
JM: "🇯🇲",
JO: "🇯🇴",
JP: "🇯🇵",
KE: "🇰🇪",
KG: "🇰🇬",
KH: "🇰🇭",
KI: "🇰🇮",
KM: "🇰🇲",
KN: "🇰🇳",
KP: "🇰🇵",
KR: "🇰🇷",
KW: "🇰🇼",
KY: "🇰🇾",
KZ: "🇰🇿",
LA: "🇱🇦",
LB: "🇱🇧",
LC: "🇱🇨",
LI: "🇱🇮",
LK: "🇱🇰",
LR: "🇱🇷",
LS: "🇱🇸",
LT: "🇱🇹",
LU: "🇱🇺",
LV: "🇱🇻",
LY: "🇱🇾",
MA: "🇲🇦",
MC: "🇲🇨",
MD: "🇲🇩",
ME: "🇲🇪",
MF: "🇲🇫",
MG: "🇲🇬",
MH: "🇲🇭",
MK: "🇲🇰",
ML: "🇲🇱",
MM: "🇲🇲",
MN: "🇲🇳",
MO: "🇲🇴",
MP: "🇲🇵",
MQ: "🇲🇶",
MR: "🇲🇷",
MS: "🇲🇸",
MT: "🇲🇹",
MU: "🇲🇺",
MV: "🇲🇻",
MW: "🇲🇼",
MX: "🇲🇽",
MY: "🇲🇾",
MZ: "🇲🇿",
NA: "🇳🇦",
NC: "🇳🇨",
NE: "🇳🇪",
NF: "🇳🇫",
NG: "🇳🇬",
NI: "🇳🇮",
NL: "🇳🇱",
NO: "🇳🇴",
NP: "🇳🇵",
NR: "🇳🇷",
NU: "🇳🇺",
NZ: "🇳🇿",
OM: "🇴🇲",
PA: "🇵🇦",
PE: "🇵🇪",
PF: "🇵🇫",
PG: "🇵🇬",
PH: "🇵🇭",
PK: "🇵🇰",
PL: "🇵🇱",
PM: "🇵🇲",
PN: "🇵🇳",
PR: "🇵🇷",
PS: "🇵🇸",
PT: "🇵🇹",
PW: "🇵🇼",
PY: "🇵🇾",
QA: "🇶🇦",
RE: "🇷🇪",
RO: "🇷🇴",
RS: "🇷🇸",
RU: "🇷🇺",
RW: "🇷🇼",
SA: "🇸🇦",
SB: "🇸🇧",
SC: "🇸🇨",
SCOT: "🏴󠁧󠁢󠁳󠁣󠁴󠁿",
SD: "🇸🇩",
SE: "🇸🇪",
SG: "🇸🇬",
SH: "🇸🇭",
SI: "🇸🇮",
SJ: "🇸🇯",
SK: "🇸🇰",
SL: "🇸🇱",
SM: "🇸🇲",
SN: "🇸🇳",
SO: "🇸🇴",
SR: "🇸🇷",
SS: "🇸🇸",
ST: "🇸🇹",
SV: "🇸🇻",
SX: "🇸🇽",
SY: "🇸🇾",
SZ: "🇸🇿",
TC: "🇹🇨",
TD: "🇹🇩",
TF: "🇹🇫",
TG: "🇹🇬",
TH: "🇹🇭",
TJ: "🇹🇯",
TK: "🇹🇰",
TL: "🇹🇱",
TM: "🇹🇲",
TN: "🇹🇳",
TO: "🇹🇴",
TR: "🇹🇷",
TT: "🇹🇹",
TV: "🇹🇻",
TW: "🇹🇼",
TZ: "🇹🇿",
UA: "🇺🇦",
UG: "🇺🇬",
UM: "🇺🇲",
US: "🇺🇸",
UY: "🇺🇾",
UZ: "🇺🇿",
VA: "🇻🇦",
VC: "🇻🇨",
VE: "🇻🇪",
VG: "🇻🇬",
VI: "🇻🇮",
VN: "🇻🇳",
VU: "🇻🇺",
WF: "🇼🇫",
WS: "🇼🇸",
YE: "🇾🇪",
YT: "🇾🇹",
ZA: "🇿🇦",
ZM: "🇿🇲",
ZW: "🇿🇼"
}[c])) AS Q_FLAG

holroy · February 11, 2023, 12:32am

That is indeed a crazy query. Could you also show how it actually looks, or the top of a result from it?

Did you get that other post regarding how the subtraction of dates could be a little off when the days exceeded over a month? Shouldn’t matter a whole lot, but I just thought I’d mention it.

ReaderGuy42 · February 11, 2023, 1:11am

I didn’t see that other post, but honestly the dates are just for estimates. Not doing science here haha

Combined with Cards, this is a sample of my gallery:

I just finished aliasing all authors and movie directors, so now all book/movie titles and all authors/directors are clickable.

ReaderGuy42 · February 11, 2023, 12:01pm

Related to this, I tried doing the same aliasing to my two-letter country code, e.g. Country: US to Country: "[[United States of America|US]]"
so I can also have a list by country page.

However, your awesome solution to my earlier problem from a few months ago, now no longer works, meaning I broke my query again:

FLATTEN join(map(split(Country, ", "), (c) => {
BZ: "🇧🇿",
CA: "🇨🇦",
US: "🇺🇸"

}[c])) AS Q_FLAG

I can’t figure out how to implement this with the aliasing, since I now separate the Countries like this:

Country: 
- "[[United States of America|US]]"
- "[[Canada|CA]]"

instead of like previously with a comma. The split command no longer works, since there’s no longer a split by commas, but removing the split thing doesn’t solve the problem.

Something like this also doesn’t work:

FLATTEN Country {
BZ: "🇧🇿",
CA: "🇨🇦",
US: "🇺🇸"

} AS Q_FLAG

Can this be fixed? Thanks!

justdoitcc · February 12, 2023, 6:01am

ReaderGuy42:

I can’t figure out how to implement this with the aliasing, since I now separate the Countries like this:
---
Country: 
- "[[United States of America|US]]"
- "[[Canada|CA]]"
---
instead of like previously with a comma.

Topic

Summary

How to to transform a country code into a flag icon via a JavaScript object?

Test

Summary

dataview: v0.5.55

Input

Summary

dictionary files:

Summary

Location: “100_Project/02_dataview/Q25_MetaFunction/Q25_test_data02”

folder: 03_a_string

filename : dic_19620306

---
Date: 1962-03-06
Country: "[[Canada|CA]]"
---

folder: 04_a_list

filename : dic_19800401

---
Date: 1962-04-06
Country: 
- "[[United States of America|US]]"
---

folder: 05_a_list

filename : dic_19800501

---
Date: 1962-05-06
Country: 
- "[[United States of America|US]]"
- "[[Canada|CA]]"
---

DQL11_transform_country_code_into_flag_icon_via_JavaScript_object_and_TABLE

Summary

Main DQL

Code Name	Data type	Group By	Purposes	Remark
DQL11 _transform_country_code _into_flag_icon _via_JavaScript_object _and_TABLE	`Country`: 1.a string like “[[Canada\|CA]]” 2.an array of strings like [“[[United States of America\|US]]”]	no	1.To filter by `Country` 2.To transform a country code into a flag icon via a JavaScript object 3.To sort by `file.name` in ascending order 4.To display the result as a table	For Windows OS (To see Notes of the DQL10 _transform_country_code _into_flag_icon _from_HTML_Hex _and_TABLE): from Q20_Flags: S1 1.To require `NotoColorEmojiLimited.css` 2.To enable the CSS snippet

Notes:

Summary

Q1: How to modify the following code?

Summary_Q1

Original Example: Q1 (To be modified and explained)

Original INPUT

filename : O1

---
Country: "US"
---

filename : O2

---
Country: "US, CA"
---

Original DQL

```dataview
TABLE WITHOUT ID
      file.link AS "FILE",
      Q_FLAG AS "Q_FLAG"
      
FROM "100_Project/02_dataview/Q25_MetaFunction/Q25_test_data02"
WHERE Country


// The input of map() method is an Array of Strings in the example.
// AoS is an abbreviation for an Array of Strings.
// O1: AoS = split(Country, ", ") = ["US"];
// O2: AoS = split(Country, ", ") = ["US", "CA"];
// c = an element of AoS;
// O1: c = "US";
// O2: c = "US", or "CA";
FLATTEN join(map(split(Country, ", "), (c) => {
BZ: "🇧🇿",
CA: "🇨🇦",
US: "🇺🇸"

}[c])) AS Q_FLAG

```

A1_11:

Another Example: A1_11

New INPUT

filename : N1

---
Country: "[[United States of America|US]]"
---

filename : N2

---
Country: 
- "[[United States of America|US]]"
- "[[Canada|CA]]"
---

New DQL

```dataview
TABLE WITHOUT ID
      file.link AS "FILE",
      Q_FLAG AS "Q_FLAG"
      
FROM "100_Project/02_dataview/Q25_MetaFunction/Q25_test_data02"
WHERE Country


// The input of map() method is an Array of Strings in the example.
// AoS is an abbreviation for an Array of Strings.
// N1: AoS = list(Country) = ["[[United States of America|US]]"];
// N2: AoS = Country = ["[[United States of America|US]]", "[[Canada|CA]]"];
// c = an element of AoS;
// N1: meta(c).display = "US";
// N2: meta(c).display = "US", or "CA";
FLATTEN join(map(choice(typeof(Country) = "array", Country, list(Country)), (c) => {
BZ: "🇧🇿",
CA: "🇨🇦",
US: "🇺🇸"

}[meta(c).display])) AS Q_FLAG

```

Code DQL11_transform_country_code_into_flag_icon_via_JavaScript_object_and_TABLE

Summary_code

title: DQL11_transform_country_code_into_flag_icon_via_JavaScript_object_and_TABLE =>0.For Windows OS:To require `NotoColorEmojiLimited.css` and To enable the CSS snippet 1.To filter by `Country` 2.To transform a country code into a flag icon via a JavaScript object 3.To sort by `file.name` in ascending order 4.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
      file.link AS "FILE",
      Q_FLAG AS "Q_FLAG"
      
FROM "100_Project/02_dataview/Q25_MetaFunction/Q25_test_data02"
WHERE Country

FLATTEN join(map(choice(typeof(Country) = "array", Country, list(Country)), (c) => {
BZ: "🇧🇿",
CA: "🇨🇦",
US: "🇺🇸"

}[meta(c).display])) AS Q_FLAG


SORT file.name ASC

```

Screenshots(DQL10): `NotoColorEmojiLimited.css` used on win7

Summary_A

Screenshots(DQL10): `NotoColorEmojiLimited.css` not used on win7

Summary_B

Reference

Summary

Q20_Flags

Q20_Flags: S1: DQL10_Notes_Q1: Solutions
Q20_Flags: S3: Solutions

ReaderGuy42 · February 12, 2023, 2:34pm

Perfect, thank you!!!

system · February 19, 2023, 2:35pm

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

Dataview "contains" help

Topic

Test

Input

dictionary files:

folder: 03_a_string

folder: 04_a_list

folder: 05_a_list

DQL11_transform_country_code_into_flag_icon_via_JavaScript_object_and_TABLE

Main DQL

Notes:

Q1: How to modify the following code?

Original Example: Q1 (To be modified and explained)

Original INPUT

Original DQL

A1_11:

Another Example: A1_11

New INPUT

New DQL

Code DQL11_transform_country_code_into_flag_icon_via_JavaScript_object_and_TABLE

Screenshots(DQL10): NotoColorEmojiLimited.css used on win7

Screenshots(DQL10): NotoColorEmojiLimited.css not used on win7

Reference

Q20_Flags

Screenshots(DQL10): `NotoColorEmojiLimited.css` used on win7

Screenshots(DQL10): `NotoColorEmojiLimited.css` not used on win7