Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Qlik Cloud Maintenance is scheduled between March 27-30. Visit Qlik Cloud Status page for more details.
Showing results for 
Search instead for 
Did you mean: 
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.



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

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)]';
	// Add results of the row to the table
	Concatenate (ResultTable)
		Peek('REF.emp_ID',$(vRow),'REF') as emp_ID
	AutoGenerate 1; 
	// Initialize script for next row
	LET vScript ='';
// 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)],';
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;