Hi all. Suppose I have 2 tables in MySQL database, Device Info and Operation Record.
For Device Info:
|----id----|—Name—|
|----1----|–Device 1–|
|----2----|–Device 2–|
|----3----|–Device 3–|
For Operation Record:
|----id----|----Date----|
|----1----|–01-07-23–|
|----2----|–01-07-23–|
|----3----|–01-07-23–|
|----1----|–02-07-23–|
When imported into QuickSight, suppose I applied a date filter and use Gauge Chart by placing counts in Operation Record as value and counts in Device Info as total value, when date = 01-07-23, I should get 100%. when date = 02-07-23 I should get 33.33%.
But with the date filter = 02-07-23 applied, how do I display the name of those devices that do not have record in the Operation Record table? The the result produced by the following SQL query is what I am trying to get in QuickSight:
SELECT Name FROM Device Info WHERE id NOT IN (SELECT a.id FROM Device Info a JOIN Operation Record b ON a.id = b.id WHERE Date = “02-07-23”);
Would appreciate it for syntax for calculated field and idea on proper visual type to display the information. Thank you very much.
