Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nicouek
Contributor III
Contributor III

Data quality check

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.

nicouek_1-1653114780489.png

 

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

 

 

Labels (1)
1 Reply
rubenmarin

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;