Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Compare columns from DB and File

Hi,

 

I have a scenario where I need to compare the data from file with data in DB. In file there are 13 columns and in data from DB there are 9 columns. I need to check if the value for 6 columns are same in DB and in file.

 

So, below are steps I have performed:

 

1) Get data from DB

2) Get data from file using file delimited component

3) Perform Unique match and Inner join on the 6 columns between DB and file in TMAP component (i.e. from input tmap).

4) Also created 6 variables (variables section in tmap) which performs a check to see if columns are same, for example : 

row3.id == row4.ID? true : false 

row3.cust == row4.CUST ? true : false

5) There are 2 outputs: Accepts and Rejects

6) In expression filters for Accepts : Added all 6 variables created with "&&" i.e. AND condition between the variables so that Accepts should have all the variables which have succeeded.

7) In output section of tmap for Rejects, have set both "Catch Output Reject" and "Catch Inner join Reject" to true.

😎 Store the above outputs i.e. Accepts and Rejects to thashoutput.

9) Also in Rejects, I have created a new variable "failure_reason" which contains the reason for failure. Example, please see below:

(Var.var1 ? "" : "id value does not match between db and ftp. FTP value : " +row4.ID+" and DB value : " + row3.id +" ; ") +
(Var.var2 ? "" : "cust value does not match between db and ftp. FTP value : " +row4.CUST+" and DB value : " + row3.cust +" ; ")

 

The problem is that I do not see the above steps performing comparison of data between file and DB correctly. I see that the outputs are not as expected (or in short the comparison is not as expected because I know what DB and file data contain).

 

Can anyone please let me know how to check if data from file and DB are same ?

Labels (2)
1 Reply
TRF
Champion II
Champion II

Depending on the datatype for each field, you have to use the appropriate method for comparison such as String.equals for strings and so on.
You may also share your tMap and sample data with expected result.