MentDB WeakMQL Recipes
← innov-ai.com All profiles Portal
Profiles / Engineering & data
DQ

Data quality analyst

Outliers, format checks, normalization and duplicate detection.

10 recipes on this page · open in the interactive view

Recipes
  1. Outliers with Tukey fences and z-scores
  2. Format checks of supplier records
  3. Near-duplicate customer names
  4. Apply the data quality rules of the server catalog
  5. Completeness, validity and uniqueness scorecard of suppliers
  6. Gaps and duplicates in an invoice number sequence
  7. Data quality flags as a bitmask, today versus yesterday
  8. Sub-cent amounts, truncation and floating point noise
  9. Outlier readings in a sensor table by density clustering
  10. Placeholder values and debit/credit sign consistency
executed on MentDB

Outliers with Tukey fences and z-scores

Invoice amounts come from an ERP extract. The script computes the Tukey fences from the quartiles and lists the values outside, with their z-score.

Pipeline
01 · quartilesQ1 and Q3stat quartiles→02 · fencesQ1 - 1.5 IQR, Q3 + 1.5 IQRmath calc→03 · zscorez of each valuestat zscore→04 · flagValues outside the fencesjson parse_array
Script
admin
#1. Invoice amounts in EUR loaded from an ERP extract;
-> "[x]" "[120, 135, 128, 142, 131, 1250, 126, 139, 133, 12, 137, 129, 141, 130, 136]";
#2. Tukey fences Q1 - 1.5 IQR and Q3 + 1.5 IQR;
json load "rx_dq_q" (stat quartiles [x]);
-> "[q1]" (json select "rx_dq_q" "/[0]"); -> "[q3]" (json select "rx_dq_q" "/[2]");
-> "[lo]" (math calc "[q1]-1.5*([q3]-[q1])"); -> "[hi]" (math calc "[q3]+1.5*([q3]-[q1])");
#3. Flag the values outside the fences, with their z-score;
json load "rx_dq_x" [x]; json load "rx_dq_z" (stat zscore [x]);
-> "[i]" 0; -> "[out]" "";
json parse_array "rx_dq_x" "/" "[v]" {
  if (or (< [v] [lo]) (> [v] [hi])) {-> "[out]" (concat [out] " " [v] " (z = " (math round (json select "rx_dq_z" (concat "/[" [i] "]")) 2) ")");};
  ++ "[i]";
};
concat "fences [" [lo] ", " [hi] "], outliers:" [out]
Result
mentdb
fences [114.25, 152.25], outliers: 1250 (z = 3.59) 12 (z = -0.64)
Go further
  • The z-score misses 12 because the 1250 outlier inflates the standard deviation (masking): prefer the fences
  • Use a robust z-score with the median and stat percentile
  • Count the values per class with stat frequency
executed on MentDB

Format checks of supplier records

Supplier records must be checked before an import. The script loops over the records and validates the email, the IBAN, the SIRET and the date of each one.

Pipeline
01 · loopEach recordjson parse_array→02 · emailEmail addresstype is_email→03 · bankIBAN and SIRET checksumstype is_iban→04 · dateReal calendar datetype is_valid_date
Script
admin
#1. Supplier records to check before an import;
json load "rx_dq_r" "[{\"id\": 1, \"email\": \"compta@acme.fr\", \"iban\": \"FR76 3000 6000 0112 3456 7890 189\", \"siret\": \"732 829 320 00074\", \"since\": \"2019-04-12\"}, {\"id\": 2, \"email\": \"sales@@globex.com\", \"iban\": \"FR76 3000 6000 0112 3456 7890 188\", \"siret\": \"73282932000074\", \"since\": \"2021-02-30\"}, {\"id\": 3, \"email\": \"info@initech.io\", \"iban\": \"DE89 3704 0044 0532 0130 00\", \"siret\": \"732 829 320 00075\", \"since\": \"2020-11-05\"}]";
#2. Check each field with the type functions;
-> "[rep]" ""; -> "[sep]" "";
json parse_array "rx_dq_r" "/" "[row]" {
  json load "rx_dq_row" [row];
  -> "[bad]" "";
  if (not (type is_email (json select "rx_dq_row" /email))) {-> "[bad]" (concat [bad] " email");};
  if (not (type is_iban (json select "rx_dq_row" /iban))) {-> "[bad]" (concat [bad] " iban");};
  if (not (type is_siret (json select "rx_dq_row" /siret))) {-> "[bad]" (concat [bad] " siret");};
  if (not (type is_valid_date (json select "rx_dq_row" /since) "yyyy-MM-dd")) {-> "[bad]" (concat [bad] " date");};
  #3. One line per record;
  -> "[rep]" (concat [rep] [sep] "record " (json select "rx_dq_row" /id) ": " (if (is null or empty [bad]) {"OK"} {(concat "invalid" [bad])}));
  -> "[sep]" " | ";
};
[rep]
Result
mentdb
record 1: OK | record 2: invalid email iban date | record 3: invalid siret
Go further
executed on MentDB

Near-duplicate customer names

Customer names were typed by different agents with accents, case and spacing differences. The script builds a normalization key and compares every pair with the edit distance.

Pipeline
01 · normalizeTrim, unaccent, lowerstring unaccent→02 · cleanRemove punctuationstring regexp_replace→03 · compareEdit distance of each pairstring levenshtein_distance
Script
admin
#1. Customer names typed by different agents;
json load "rx_dq_n" "[\"  Hélène  MARTIN \", \"helene martin\", \"Jean-Luc Dupont\", \"Jean Luc  Dupond\", \"Sophie Bernard\"]";
#2. Normalization key: trim, no accent, lower case, no punctuation;
-> "[keys]" "["; -> "[sep]" "";
json parse_array "rx_dq_n" "/" "[n]" {
  -> "[keys]" (concat [keys] [sep] "\"" (string regexp_replace (string lower (string unaccent (string trim [n]))) "[^a-z ]" " ") "\"");
  -> "[sep]" ",";
};
json load "rx_dq_k" (concat [keys] "]");
#3. Compare every pair of keys with the edit distance;
-> "[dup]" ""; -> "[nb]" (json count "rx_dq_k" /);
for (-> "[i]" 0) (< [i] [nb]) (++ "[i]") {
  for (-> "[j]" (+ [i] 1)) (< [j] [nb]) (++ "[j]") {
    -> "[d]" (string levenshtein_distance (json select "rx_dq_k" (concat "/[" [i] "]")) (json select "rx_dq_k" (concat "/[" [j] "]")));
    if (<= [d] 2) {-> "[dup]" (concat [dup] " #" [i] "~#" [j] " (d=" [d] ")");};
  };
};
concat "keys " (json doc "rx_dq_k") " ; probable duplicates:" [dup]
Result
mentdb
keys [
  "helene martin",
  "helene martin",
  "jean luc dupont",
  "jean luc dupond",
  "sophie bernard"
] ; probable duplicates: #0~#1 (d=0) #2~#3 (d=1)
Go further
  • Use string sentence_distance for longer texts
  • Compare the phonetic keys of the names
  • Add the postcode to the key to avoid false duplicates
executed on MentDB

Apply the data quality rules of the server catalog

Before profiling a contact table, list the data quality rules of the server catalog, read the rules is_email and is_integer, and apply the same checks to an extract of the email and age columns to get a validity rate per column and the rows to fix.

Pipeline
01 · catalogRules of the serverdq algorithm show→02 · ruleMQL of a ruledq algorithm get→03 · existRule available?dq algorithm exist→04 · checkEmail and integer checkstype is_email→05 · emptyMissing valuesis null or empty
Script
admin
#1. Rules of the data quality catalog of the server;
json load "rx_dq" (dq algorithm show);
-> "[n]" (json count "rx_dq" /);
#2. Read the rules we will apply to the contact table;
-> "[r_mail]" (string trim (dq algorithm get "is_email"));
-> "[r_int]" (string trim (dq algorithm get "is_integer"));
#3. Apply the same checks to an extract of the columns email and age;
json load "rx_rows" "[[\"anna.lee@mail.com\", \"34\"], [\"bob@mail\", \"41\"], [\"\", \"x\"], [\"c.diaz@shop.fr\", \"127000\"], [\"d.wu@corp.io\", \"29\"], [\"eve@corp.io\", \"\"]]";
-> "[okm]" 0; -> "[oka]" 0; -> "[bad]" "";
for (-> "[i]" 0) (< [i] (json count "rx_rows" /)) (++ "[i]") {
  -> "[m]" (json select "rx_rows" (concat "/[" [i] "]/[0]"));
  -> "[g]" (json select "rx_rows" (concat "/[" [i] "]/[1]"));
  -> "[vm]" (type is_email [m]);
  -> "[va]" (if (is null or empty [g]) {0} {type is_integer [g] 3});
  -> "[okm]" (+ [okm] [vm]); -> "[oka]" (+ [oka] [va]);
  if (== (* [vm] [va]) 0) {-> "[bad]" (concat [bad] " row " (+ [i] 1));};
};
#4. Validity rate per column;
concat [n] " rules in the catalog, is_email exists: " (dq algorithm exist "is_email") " | rule is_email = " [r_mail] " , rule is_integer = " [r_int] " | email valid " (math round (* 100 (/ [okm] 6)) 1) " pct, age valid " (math round (* 100 (/ [oka] 6)) 1) " pct | rows to fix:" [bad]
Result
mentdb
24 rules in the catalog, is_email exists: 1 | rule is_email = type is_email [VAR]; , rule is_integer = type is_integer [VAR] 8; | email valid 66.7 pct, age valid 50 pct | rows to fix: row 2 row 3 row 4 row 6
Go further
  • dq generate writes the full analysis script of a database column from a list of rule keys.
  • dq algorithm set adds a company rule, for example a regex of the customer code.
executed on MentDB

Completeness, validity and uniqueness scorecard of suppliers

An extract of a supplier master data is checked on the classic dimensions of data quality: completeness of the mandatory fields, validity of the SIRET (Luhn), of the French phone number and of the overseas postal code, and uniqueness of the business key.

Pipeline
01 · loadSupplier recordsjson load→02 · completeName and SIRET filledis null or empty→03 · validSIRET Luhn and phonetype is_siret→04 · formatPostal code regexstring regexp_count→05 · uniqueDuplicated idsstring count
Script
admin
#1. Extract of a supplier master: id, company, SIRET, phone, postal code;
json load "rx_sup" "[{\"id\": \"S01\", \"name\": \"Batiplus\", \"siret\": \"732 829 320 00074\", \"phone\": \"02 62 41 22 10\", \"zip\": \"97400\"}, {\"id\": \"S02\", \"name\": \"Elec Sud\", \"siret\": \"732 829 320 00075\", \"phone\": \"+262 692 12 34 56\", \"zip\": \"97410\"}, {\"id\": \"S03\", \"name\": \"\", \"siret\": \"552 100 554 00025\", \"phone\": \"0692 12 34\", \"zip\": \"9741\"}, {\"id\": \"S02\", \"name\": \"Elec Sud\", \"siret\": \"732 829 320 00074\", \"phone\": \"0262 41 22 10\", \"zip\": \"97410\"}, {\"id\": \"S05\", \"name\": \"Agro Est\", \"siret\": \"\", \"phone\": \"06 12 34 56 78\", \"zip\": \"97470\"}]";
-> "[n]" (json count "rx_sup" /);
#2. Completeness of the mandatory fields, validity of SIRET by Luhn, phone and postal code;
-> "[full]" 0; -> "[siret]" 0; -> "[phone]" 0; -> "[zip]" 0; -> "[ids]" "";
for (-> "[i]" 0) (< [i] [n]) (++ "[i]") {
  -> "[r]" (concat "/[" [i] "]");
  if (and (not (is null or empty (json select "rx_sup" (concat [r] "/name")))) (not (is null or empty (json select "rx_sup" (concat [r] "/siret"))))) {-> "[full]" (+ [full] 1);};
  -> "[siret]" (+ [siret] (type is_siret (json select "rx_sup" (concat [r] "/siret"))));
  -> "[phone]" (+ [phone] (type is_phone_fr (json select "rx_sup" (concat [r] "/phone"))));
  -> "[zip]" (+ [zip] (string regexp_count (json select "rx_sup" (concat [r] "/zip")) "^97[1-6][0-9]{2}$"));
  -> "[ids]" (concat [ids] (json select "rx_sup" (concat [r] "/id")) " ");
};
#3. Uniqueness of the business key id;
-> "[dup]" 0;
for (-> "[i]" 0) (< [i] [n]) (++ "[i]") {
  if (> (string count [ids] (concat (json select "rx_sup" (concat "/[" [i] "]/id")) " ")) 1) {-> "[dup]" (+ [dup] 1);};
};
#4. Scorecard in percent;
concat "completeness " (* 100 (/ [full] [n])) " pct | SIRET valid " (* 100 (/ [siret] [n])) " pct | phone valid " (* 100 (/ [phone] [n])) " pct | postal code valid " (* 100 (/ [zip] [n])) " pct | rows sharing an id: " [dup]
Result
mentdb
completeness 60 pct | SIRET valid 40 pct | phone valid 80 pct | postal code valid 80 pct | rows sharing an id: 2
Go further
executed on MentDB

Gaps and duplicates in an invoice number sequence

Auditors require a continuous invoice numbering. The extract of the month arrives unsorted from two systems; the script sorts the numbers as longs, then lists the duplicates and the missing numbers between the first and the last one.

Pipeline
01 · sortOrdered array of longsmath order_long→02 · scanCompare each number with the previous onejson select→03 · formatMissing numbers without exponentmath decimal_format
Script
admin
#1. Invoice numbers of October from the shop and the ERP;
-> "[inv]" "[2026100417, 2026100409, 2026100410, 2026100412, 2026100411, 2026100415, 2026100410, 2026100416, 2026100413, 2026100419, 2026100420, 2026100416]";
-> "[sorted]" (math order_long [inv]);
json load "rx_p4g_inv" [sorted];
-> "[n]" (json count "rx_p4g_inv" /);
-> "[dup]" ""; -> "[gap]" ""; -> "[nmiss]" 0;
#2. Scan the sorted list;
for (-> "[i]" 1) (< [i] [n]) (++ "[i]") {
  -> "[a]" (json select "rx_p4g_inv" (concat "/[" (- [i] 1) "]"));
  -> "[b]" (json select "rx_p4g_inv" (concat "/[" [i] "]"));
  if (== [a] [b]) {-> "[dup]" (concat [dup] " " [b]);};
  for (-> "[m]" (+ [a] 1)) (< [m] [b]) (++ "[m]") {-> "[gap]" (concat [gap] " " (math decimal_format [m] "0" "." " ")); ++ "[nmiss]";};
};
-> "[first]" (json select "rx_p4g_inv" "/[0]");
-> "[last]" (json select "rx_p4g_inv" (concat "/[" (- [n] 1) "]"));
json unload "rx_p4g_inv";
concat "range " [first] " - " [last] ", " [n] " lines" [_n_] "duplicates:" [dup] [_n_] "missing:" [gap] [_n_] "completeness = " (math round (* 100 (/ (- (+ (- [last] [first]) 1) [nmiss]) (+ (- [last] [first]) 1))) 1) " %"
Result
mentdb
range 2026100409 - 2026100420, 12 lines
duplicates: 2026100410 2026100416
missing: 2026100414 2026100418
completeness = 83.3 %
Go further
  • Check the dates are increasing with the numbers using date cmp.
  • Store the anomalies in a tuto_ node to follow them month after month.
executed on MentDB

Data quality flags as a bitmask, today versus yesterday

Each customer record gets one integer flag: bit 1 = invalid email, bit 2 = invalid IBAN, bit 4 = negative balance, bit 8 = invalid birth date. The script builds the flags with bit_or, counts each rule with bit_and, and uses bit_xor against yesterday's flags to see what was fixed and what broke.

Pipeline
01 · checksEmail, IBAN, date validitytype is_iban→02 · combineSet the failed bitsmath bit_or→03 · countRecords failing each rulemath bit_and→04 · diffChanged bits since yesterdaymath bit_xor
Script
admin
#1. Records of the day and the flags computed yesterday;
json load "rx_p4g_cust" "[{\"email\": \"ana@shop.fr\", \"iban\": \"FR76 3000 6000 0112 3456 7890 189\", \"balance\": 120, \"birth\": \"1985-04-12\"}, {\"email\": \"bob@shop\", \"iban\": \"FR76 3000 6000 0112 3456 7890 189\", \"balance\": -40, \"birth\": \"1990-02-30\"}, {\"email\": \"cid@shop.fr\", \"iban\": \"FR76 3000 6000 0112 3456 7890 180\", \"balance\": 15, \"birth\": \"1977-11-03\"}, {\"email\": \"dan@shop.fr\", \"iban\": \"FR76 3000 6000 0112 3456 7890 189\", \"balance\": -5, \"birth\": \"2001-07-21\"}]";
-> "[yesterday]" "[0, 7, 0, 2]";
json load "rx_p4g_y" [yesterday];
-> "[out]" ""; -> "[c1]" 0; -> "[c2]" 0; -> "[c4]" 0; -> "[c8]" 0;
for (-> "[i]" 0) (< [i] 4) (++ "[i]") {
  -> "[p]" (concat "/[" [i] "]");
  #2. Build the flag;
  -> "[f]" 0;
  if (not (type is_email (json select "rx_p4g_cust" (concat [p] "/email")))) {-> "[f]" (math bit_or [f] 1);};
  if (not (type is_iban (json select "rx_p4g_cust" (concat [p] "/iban")))) {-> "[f]" (math bit_or [f] 2);};
  if (< (json select "rx_p4g_cust" (concat [p] "/balance")) 0) {-> "[f]" (math bit_or [f] 4);};
  if (not (type is_valid_date (json select "rx_p4g_cust" (concat [p] "/birth")) "yyyy-MM-dd")) {-> "[f]" (math bit_or [f] 8);};
  #3. Count per rule and compare with yesterday;
  if (> (math bit_and [f] 1) 0) {++ "[c1]";};
  if (> (math bit_and [f] 2) 0) {++ "[c2]";};
  if (> (math bit_and [f] 4) 0) {++ "[c4]";};
  if (> (math bit_and [f] 8) 0) {++ "[c8]";};
  -> "[y]" (json select "rx_p4g_y" (concat "/[" [i] "]"));
  -> "[chg]" (math bit_xor [f] [y]);
  -> "[out]" (concat [out] "rec " [i] ": flag " [f] ", fixed bits " (math bit_and [chg] [y]) ", new bits " (math bit_and [chg] [f]) [_n_]);
};
json unload "rx_p4g_cust"; json unload "rx_p4g_y";
concat [out] "email " [c1] ", iban " [c2] ", negative balance " [c4] ", birth date " [c8]
Result
mentdb
rec 0: flag 0, fixed bits 0, new bits 0
rec 1: flag 13, fixed bits 2, new bits 8
rec 2: flag 2, fixed bits 0, new bits 2
rec 3: flag 4, fixed bits 2, new bits 4
email 1, iban 1, negative balance 2, birth date 1
Go further
  • Keep the flags in a tuto_ node per day to draw a trend.
  • Turn the flag into a list of rule names with a for over the powers of 2.
executed on MentDB

Sub-cent amounts, truncation and floating point noise

A payment file must hold amounts with at most 2 decimals. Because 19.99 x 100 is not exactly 1999 in binary floating point, the script compares x . 100 with its nearest integer using a tolerance of a few ulps, flags real sub-cent amounts, and shows what a legacy job that truncates instead of rounding loses.

Pipeline
01 · nearestNearest integer of centsmath rint→02 · toleranceSize of an ulpmath ulp→03 · fractionFractional part of centsmath frac_part→04 · truncationTruncated vs roundedmath trunc
Script
admin
#1. Amounts of the file;
json load "rx_p4g_amt" "[19.99, 0.1, 250.005, 1234.56, 7.123, 0.3, 99.999]";
-> "[bad]" ""; -> "[loss]" 0;
for (-> "[i]" 0) (< [i] (json count "rx_p4g_amt" /)) (++ "[i]") {
  -> "[x]" (json select "rx_p4g_amt" (concat "/[" [i] "]"));
  -> "[c]" (* [x] 100);
  #2. Real sub-cent amount if c is far from an integer, more than 16 ulps;
  if (> (math abs (- [c] (math rint [c]))) (* 16 (math ulp [c]))) {
    -> "[bad]" (concat [bad] " " [x] " (decimals " (math frac_part [x]) ")");
  };
  #3. Cents lost by a truncating job;
  -> "[loss]" (+ [loss] (- (math round [x] 2) (/ (math trunc [c]) 100)));
};
json unload "rx_p4g_amt";
concat "binary noise: 19.99 x 100 = " (* 19.99 100) ", 0.1 + 0.2 = " (+ 0.1 0.2) [_n_] "sub-cent amounts:" [bad] [_n_] "truncation instead of rounding loses " (math round [loss] 2) " EUR on this file"
Result
mentdb
binary noise: 19.99 x 100 = 1998.9999999999998, 0.1 + 0.2 = 0.30000000000000004
sub-cent amounts: 250.005 (decimals 0.005) 7.123 (decimals 0.123) 99.999 (decimals 0.999)
truncation instead of rounding loses 0.03 EUR on this file
Go further
  • Use math round with 2 decimals at the source and keep amounts as cents in the database.
  • Count the bad amounts per supplier with json and stat frequency.
executed on MentDB

Outlier readings in a sensor table by density clustering

Temperature and humidity readings of a cold room are stored in a database table (embedded H2 here). The data quality analyst loads them from SQL into a density clustering: readings that join no dense cluster are the suspicious ones (sensor glitch, door left open).

Pipeline
01 · connectionRegister the databasecm set h2_embedded→02 · dataTable of readingssql dml→03 · clusterLoad and cluster from SQLml cluster load→04 · registryCluster registeredml cluster show→05 · noiseReadings outside any clusterml cluster nb_point
Script
admin
#1. Readings table, embedded H2 file under tmp;
cm set "tuto_p4g_iot" {execute "db.h2.embedded.config.get"
	"[database]" "./tmp/tuto_p4g_iot/coldroom"
	"[user]" "tuto"
	"[password]" "tuto_pwd"
;};
sql connect "tuto_p4g_s3" {cm get "tuto_p4g_iot";};
sql dml "tuto_p4g_s3" "create table reading (temp double, hum double)";
sql dml "tuto_p4g_s3" "insert into reading values (3.1, 82), (3.4, 81), (2.9, 83), (3.2, 82), (3.0, 84), (3.3, 83), (3.1, 81), (2.8, 82), (3.5, 82), (3.2, 80), (9.8, 65), (3.0, 83), (-12.0, 82), (3.3, 81)";
-> "[total]" (sql value "tuto_p4g_s3" "select count(*) from reading");
sql disconnect "tuto_p4g_s3";
#2. Density clustering straight from the query: radius 2, at least 4 readings;
ml cluster load "rx_p4g_room" "tuto_p4g_iot" "temp" "hum" 2 4 "select temp, hum from reading";
-> "[k]" (ml cluster nb "rx_p4g_room");
-> "[in]" 0;
for (-> "[i]" 0) (< [i] [k]) (++ "[i]") {-> "[in]" (+ [in] (ml cluster nb_point "rx_p4g_room" [i]));};
-> "[r]" (concat "registered: " (string contains (ml cluster show) "rx_p4g_room") ", clusters: " [k] [_n_] "readings: " [total] ", in clusters: " [in] ", suspicious: " (- [total] [in]) " (" (math round (* 100 (/ (- [total] [in]) [total])) 1) " %)");
#3. Close and remove the temporary database;
ml cluster close "rx_p4g_room";
cm remove "tuto_p4g_iot";
file delete "tmp/tuto_p4g_iot";
[r]
Result
mentdb
registered: 1, clusters: 1
readings: 14, in clusters: 12, suspicious: 2 (14.3 %)
Go further
  • List the suspicious rows with a sql query that excludes the cluster points.
  • Show the clusters with ml cluster xy_scatter in the editor.
executed on MentDB

Placeholder values and debit/credit sign consistency

In a ledger extract, some systems write 9999.99 when the amount is unknown, and some credit lines carry a positive amount. The script finds the placeholder with the mode and the frequency table, measures how it biases the mean, and checks that the sign of each amount matches its D/C code.

Pipeline
01 · placeholderMost frequent valuestat mode→02 · shareFrequency of the valuestat frequency→03 · biasMean with and without itmath avg→04 · signSign of each amount vs D/Cmath sign
Script
admin
#1. Ledger lines: code D = debit must be positive, C = credit must be negative;
json load "rx_p4g_led" "[[\"D\", 120.5], [\"C\", -80], [\"D\", 9999.99], [\"D\", 45.2], [\"C\", 60], [\"D\", 9999.99], [\"C\", -15.75], [\"D\", 230], [\"D\", 9999.99], [\"C\", -42]]";
-> "[all]" ""; -> "[clean]" ""; -> "[sign]" "";
for (-> "[i]" 0) (< [i] 10) (++ "[i]") {
  -> "[cd]" (json select "rx_p4g_led" (concat "/[" [i] "][0]"));
  -> "[x]" (json select "rx_p4g_led" (concat "/[" [i] "][1]"));
  -> "[all]" (concat [all] "," [x]);
  if (!= [x] 9999.99) {-> "[clean]" (concat [clean] "," [x]);};
  #2. Sign check;
  -> "[want]" (if (equal [cd] "D") {1;} {-1;});
  if (!= (math sign [x]) [want]) {-> "[sign]" (concat [sign] " line " (+ [i] 1) " " [cd] " " [x]);};
};
json unload "rx_p4g_led";
-> "[all]" (concat "[" (string substr [all] 1) "]");
-> "[clean]" (concat "[" (string substr [clean] 1) "]");
#3. Placeholder: the mode, and its share in the frequency table;
-> "[ph]" (string regexp_replace (stat mode [all]) "[\[\]]" "");
json load "rx_p4g_fq" (stat frequency [all]);
-> "[share]" (json select "rx_p4g_fq" (concat "/[" (- (json count "rx_p4g_fq" /) 1) "]/frequency"));
json unload "rx_p4g_fq";
concat "placeholder suspected: " [ph] " on " (math round (* 100 [share]) 0) " % of the lines" [_n_] "mean with it = " (math round (math avg [all]) 2) ", without = " (math round (math avg [clean]) 2) [_n_] "sign errors:" [sign]
Result
mentdb
placeholder suspected: 9999.99 on 30 % of the lines
mean with it = 3031.79, without = 45.42
sign errors: line 5 C 60
Go further
  • Replace the placeholder by null in the target with string replace before loading.
  • Add the rule to the server catalog of dq algorithm.

Also in Engineering & data

TR
Telecom & radar engineer
Link budgets in dB, radar equation, detection thresholds and spectra of modulated signals.
10 recipes
CE
Control engineer
Transfer functions, margins, PID and state feedback: complex numbers, CAS, ODE and matrices.
10 recipes
RE
Robotics engineer
Kinematics, Jacobians and trajectories: matrices, vectors, non-linear systems and splines.
10 recipes
EE
Electrical engineer
Impedances, three-phase power and cable sizing with complex numbers and units.
10 recipes
ES
Electronics & signal engineer
Filter design, spectra, peak detection and SNR on sampled signals.
10 recipes
IS
IoT & sensor engineer
Sensor calibration, smoothing, drift detection and measurement uncertainty.
10 recipes
MC
Mechanical & civil engineer
Deflection, stresses, safety factors and concrete strength statistics.
10 recipes
IQ
Industrial quality engineer
SPC control limits, process capability and tests on machines.
10 recipes
DS
Data scientist
Regression, correlation, feature scaling and drift tests.
10 recipes
SB
Statistician & biostatistician
Confidence intervals, sample size, ANOVA, chi-square and non-parametric tests.
10 recipes