Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Table2:
load * inline [
field2
G
B
A
I
];
Output:
load * inline [
field1
A
B
C
D
E
F
G
H
I
]
Where Not Exists (field2, field1);
Table2:
load * inline [
field2
G
B
A
I
];
Output:
load * inline [
field1
A
B
C
D
E
F
G
H
I
]
Where Not Exists (field2, field1);
A different solution could be
Table1:
load * inline [
field1
A
B
C
D
E
F
G
H
I
];
Table2:
load * inline [
field2
G
B
A
I
];
Map:
Mapping load field2, 'FOUND' Resident Table2;
Output:
NoConcatenate load * Resident Table1
where ApplyMap('Map', field1, 'MISSING') = 'MISSING';
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;
Thank you guys for the solutions! The first worked fantastically, but I intend on trying the rest soon.