Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Not applicable

How can you view rows that are in one table and not another?

I have loaded 2 tables with exactly the same columns: TABLE_A and TABLE_B.

I want to be able to view the rows that are in TABLE_B but not in TABLE_A. The 2 tables are very large (around 160 million rows) and there are around 5 million extra rows in TABLE_B so the most efficient way of doing this would be very helpful. Just being able to see some of the rows that are different would still be helpful.

Many thanks!

1 Solution

Accepted Solutions
narender123
Valued Contributor

Re: How can you view rows that are in one table and not another?

Hi Afraz,

You can try "not exits" function.

See the attachment.

Thanks.

Narender

View solution in original post

9 Replies
Highlighted

Re: How can you view rows that are in one table and not another?

Take a look at the document Test_Join posted here

http://community.qlik.com/thread/90909

narender123
Valued Contributor

Re: How can you view rows that are in one table and not another?

Hi Afraz,

You can try "not exits" function.

See the attachment.

Thanks.

Narender

View solution in original post

Not applicable

Re: How can you view rows that are in one table and not another?

Thanks Andrea.

I have had a look at your 'Test Join' file and it almost looks like what I need. However, instead of seeing matching rows I need to see the rows that don't match. How should I do this?

Re: How can you view rows that are in one table and not another?

The qvw I suggested contains a lisbox named "Flag"...select "Table 1 rows" or "Table 2 rows" and you will see only rows that belongs to Table 1 or Table 2.

Another way to achieve your needs is to use not exists in your load statement.

Not applicable

Re: How can you view rows that are in one table and not another?

Thanks Narender.

Will your example also work if I am doing a SQL SELECT from a DB? Something like:

Table_1:

LOAD

Key,

'QVD' as Source

FROM[QVD Path](qvd);

join

Table_2:

SQL SELECT

Key,

'DB' as Source

FROM "DB"

WHERE NOT EXISTS(Key);

narender123
Valued Contributor

Re: How can you view rows that are in one table and not another?

Yes it will work.

NOT Exist also used in sql.

Narender.

Not applicable

Re: How can you view rows that are in one table and not another?

I am still having trouble with this. When I execute the above script I get a SQL Select error. I don't want the 'Where not exists' to be executed in the DB, it should be done once the data has been loaded when joining to Table_1. Is there another way to do this or have I not understood the 'Where not Exists' correctly?

narender123
Valued Contributor

Re: How can you view rows that are in one table and not another?

When you fetch the data from your sql database then then it looks like as

LOAD "OFFICE_CODE",

    "LOCATION_CODE",

    NAME,

    ACTIVE,

SQL SELECT *

FROM CIIS.dbo."REF_OFFICE_LOCATION"   WHERE NOT EXISTS(yourfield);

Please check.

MVP & Luminary
MVP & Luminary

Re: How can you view rows that are in one table and not another?

Assuming your tables have a primary key and you want to load all the data from both tables. You can create a flag field that indicates if the record from table B is a duplicate:

Data:

sql select PrimaryKeyField, field2, field3, ...., 'source1' as source' from sourcetable1;

concatenate(Data)

load *, if(exists(PrimaryKeyField),1,0) as FlagDuplicate;

sql select PrimaryKeyField, field2, field3, ....,  'source2' as source' from sourcetable2;

You can then select 1 in a listbox that has FlagDuplicate as field to find the records that are duplicates, or select 0 to find the records from table B that are not a duplicate.


talk is cheap, supply exceeds demand