topic Re: Compare Tables in QlikView

Compare Tables

Mon, 22 Aug 2016 19:07:27 GMT

Good afternoon Qlik Developers,

I have two tables with partially matching data. Table 1 is "all" data and Table 2 has "partial" data. The ideal output would be to return all non-repeated data as in the Output table below. Essentially the output is the difference between Table 1 and Table 2.

Please help me with ideas if possible, thank you! -Mack

Table 1
A
B
C
D
E
F
G
H
I

Table 2
G
B
A
I

Output
C
D
E
F
H

Re: Compare Tables

maxgro — Mon, 22 Aug 2016 20:13:04 GMT

Table2:

load * inline [

field2

];

Output:

load * inline [

field1

]

Where Not Exists (field2, field1);

Re: Compare Tables

maxgro — Mon, 22 Aug 2016 20:44:11 GMT

A different solution could be

Table1:

load * inline [

field1

];

Table2:

load * inline [

field2

];

Map:

Mapping load field2, 'FOUND' Resident Table2;

Output:

NoConcatenate load * Resident Table1

where ApplyMap('Map', field1, 'MISSING') = 'MISSING';

Re: Compare Tables

othniel2014 — Mon, 22 Aug 2016 21:21:16 GMT

Script:

Table1:

LOAD T1,

T1amount

FROM

Tabla.xlsx

(ooxml, embedded labels, table is Tabla1);

Table2:

LOAD T2 as T1,

T2,

T2amount

FROM

Tabla.xlsx

(ooxml, embedded labels, table is Tabla2);

Aux:load

T1 as Aux

Resident Table1;

Left Join (Aux)load

T2 as Aux,

Sum(T2amount) as AuxAmount

Resident Table2

Group By T2;

OutPut:LOAD

Aux as OP,

Aux as T1

Resident Aux Where IsNull(AuxAmount);

Left Join(OutPut)load

T1,

T1amount as OPamount

Resident Table1;

DROP Table Aux;

Re: Compare Tables

Tue, 23 Aug 2016 16:50:08 GMT

Thank you guys for the solutions! The first worked fantastically, but I intend on trying the rest soon.