DQ: Develop more sophisticated DQ checking

[RDmitchell]

Expected Behavior

Users have developed spreadsheets to enable sophisticated data quality checking.

Here are a few examples:

• If Building Type = Multifamily AND (Electricity Use < 15% of total energy OR ENERGY STAR SCORE > 95), flag (label) the record as "Problem"
• Create a new field based on other fields (may involve math), then do DQ on that new field
  • Calculate % of electricity mix == the ratio of total electricity consumption (which might be the sum of several fields and which requires converting kWh to kBtu) to Total Site Energy
  • then have a check if the % electricity mix > N, flag (label) it

I can put together some specific examples from cities who are already doing these DQ checks in their spreadsheets.

We need to come up with a way for users to construct these "query expressions" in SEED -- maybe SEED can interpret SQL statements as a start ?

Steps to Reproduce

Can't do this in SEED

Instance Information

instance: LBNL staging/ production
SHA: 36cc59d

[RDmitchell]

Another flavor of this (SF) which might be a bit simpler

Have AND or OR for multiple labels, then want an EXCLUDE on that result

• Show "Label 1" AND "Label 2", then EXCLUDE records from that result that have "Label 3" AND "Label 4"

And probably every variant that anyone can think of.

So basically the ability to define a custom query based on standard query construction parameters. such as are found in SQL, etc.

[RDmitchell]

something along the lines defined here (?)
https://www.w3schools.com/sql/sql_operators.asp

[RDmitchell]

Additional feedback from users about this type of functionality (taken from #1358 which is now closed)

From Ufei (NYC)
NYC requires all buildings above 50,000 sq. ft. to comply - more specifically, any one building above 50,000 sq. ft. or any two buildings on the same lot above 100,000 sq. ft. together. This check requires review of two fields - number of buildings and GFA. And we should flag anything less than 50K for one building and 100K for two or more buildings because they just don't fit under the criteria of the law.

• Set Building Count = 1, Gross Floor Area < 50000
• Set Building Count > 1, Gross Floor Area < 100000