topic Re: two tables - need to compare two columns from each one in QlikView

two tables - need to compare two columns from each one

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello! Thanks so much for taking the time to read this and potentially help me out!

I am importing two excel documents into Qlikview. They have the same columns (but I have given them different dimension names). I need to compare one column in each table that contain the same values (ie to see if table1_colA is missing any values that are present in table2_colA). After this, I need to look at all the values associated with table2_colA (such as table2_colB, table2_colC, etc). How would I do this?

I tried doing a join and then a noconcanteate with true and false values, but that ended up appending the two tables and so through off my whole spreadsheet. Any suggestions or advice would be most appreciated!!

Re: two tables - need to compare two columns from each one

Anonymous — Thu, 26 Jan 2017 11:34:09 GMT

Can you please the below script. It should create 2 tables despite the same column names.

QUALIFY *;
TabA:
LOAD * INLINE [
Col1, Col2, Col3
A, B, C
A, , C
];

TabB:
NoConcatenate
LOAD * INLINE [
Col1, Col2, Col3
A, B, C
A, , C
];

Re: two tables - need to compare two columns from each one

vinieme12 — Thu, 26 Jan 2017 11:37:19 GMT

create mapping tables from both tables of the values you want to compare

example we want to see if Table1 key exists in table2

Table1 has fields Key,A,B,C

Table2 has fields Key,X,Y,Z

MapTAB_1:

Mapping Load Key,'Exist in Table1'

From YourSource;

MapTAB_2:

Mapping Load Key,'Exist in Table2'

From YourSource;

Table1:

Load Key,A,B,C,Applymap('MapTAB_2',Key,'Does not Exist in Tab2') as Exists_in

From YourSource;

Table2:

Load Key,X,Y,Z,Applymap('MapTAB_1',Key,'Does not Exist in Tab2') as Exists_in

From YourSource;

so during table1 load you check for the values from table2 and vice versa