App Development

[nicouek]

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

[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;