Data quality check

nicouek — Sat, 21 May 2022 06:35:38 GMT

Good Morning!

I kindly need some insight about Qlik Sense Thank you

I have these two tables from the company database.
The reference table contains the correct information desired.
TABLE 1 contains the same information with missing value in some fields
and misspellings.

QUESTION: Is there a way I can use Qlik Sense to automate a comparison between the 2 table's fields
and return rows that have mistakes in TABLE1?

Thank you

Re: Data quality check

rubenmarin — Sat, 21 May 2022 07:36:59 GMT

Hi, you first have to ensure that both tables has the same number of records and sorted in the same way, then you can use this cript to compare both tables:

// Variable with all field names SET vFields ="'emp_ID','first_name','Middle initial','last_name'"; // add the needed fields // Initialize script and result table LET vScript =''; ResultTable: LOAD * Inline [dumbField]; // Irerate all rows FOR vRow=0 to NoOfRows('REF') // Add the comparison for each field FOR Each vField in $(vFields) LET vScript = '$(vScript)' & ', If(Peek('&chr(39)&'REF.$(vField)'&chr(39)&',$(vRow),'&chr(39)&'REF'&chr(39)&')=Peek('&chr(39)&'TABLE1.$(vField)'&chr(39)&',$(vRow),'&chr(39)&'TABLE1'&chr(39)&'),0,1) as [chk$(vField)]'; NEXT // Add results of the row to the table Concatenate (ResultTable) LOAD Peek('REF.emp_ID',$(vRow),'REF') as emp_ID $(vScript) AutoGenerate 1; // Initialize script for next row LET vScript =''; NEXT // Drop unnecesary fields DROP field dumbField; // Add script to check if there was any difference in the row LET vScript ='RangeMax('; FOR Each vField in $(vFields) LET vScript = '$(vScript)' & '[chk$(vField)],'; NEXT LET vScript =Left('$(vScript)',Len('$(vScript)')-1) & ') as hasDifferences'; // Add Result of the row to the table Left Join (ResultTable) LOAD emp_ID, $(vScript) Resident ResultTable;

topic Re: Data quality check in App Development

Data quality check

Re: Data quality check