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

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Exclude common data from a Table

Hi Guys,

I have two tables A and B..I need to report on data i.e.A-B(the values which is only in A not common with B).How I can do this in the script?

Table A

IDNameVal
1XXX11
2PQR12
3RST13
4FFF14
5GGG16


TableB

IDNameVal
1XXX40
2PQR50
6TTT60
7SSS70
8UUU80

Final Table should be (A-B)

IDNameValue
3RST13
4FFF14
5GGG16

Thanks....

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

Hi Pooja,

Use a WHERE clause with the EXISTS() function

NotToLoadInA:

LOAD Name

FROM TableB;

TableA:

LOAD *

FROM TableA

WHERE NO EXISTS(Name);

DROP TABLE NotToLoadInA;

Or

SourceTableA:
LOAD * INLINE [
ID, NameA, Val
1, XXX, 11
2, PQR, 12
3, RST, 13
4, FFF, 14
5, GGG, 16
];

SourceTableB:
LOAD * INLINE [
ID, NameB, Val
1, XXX, 40
2, PQR, 50
6, TTT, 60
7, SSS, 70
8, UUU, 80
];

ResultTable:
NOCONCATENATE LOAD *
RESIDENT SourceTableA
WHERE NOT EXISTS(NameB, NameA);

DROP TABLES SourceTableA, SourceTableB;

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

View solution in original post

2 Replies
Miguel_Angel_Baeyens

Hi Pooja,

Use a WHERE clause with the EXISTS() function

NotToLoadInA:

LOAD Name

FROM TableB;

TableA:

LOAD *

FROM TableA

WHERE NO EXISTS(Name);

DROP TABLE NotToLoadInA;

Or

SourceTableA:
LOAD * INLINE [
ID, NameA, Val
1, XXX, 11
2, PQR, 12
3, RST, 13
4, FFF, 14
5, GGG, 16
];

SourceTableB:
LOAD * INLINE [
ID, NameB, Val
1, XXX, 40
2, PQR, 50
6, TTT, 60
7, SSS, 70
8, UUU, 80
];

ResultTable:
NOCONCATENATE LOAD *
RESIDENT SourceTableA
WHERE NOT EXISTS(NameB, NameA);

DROP TABLES SourceTableA, SourceTableB;

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Not applicable
Author

Thank You so much Miguel..it worked.