How to find difference between two data sets

[#1]

I have a need to find the difference between two data sets.

In SQL I can do this by doing something like:

Select * from Premium where AccountNumber not in (Select ACCOUNTNO from FinacialMaster)

Select * from FinacialMaster where ACCOUNTNO not in (Select AccountNumber from Premium)

But I'm not sure how I can do something similar in Qlikview. Set Analysis might work - but I don't even know how I would do that with set analysis.

Does anyone know how you can do this kind of thing (find the difference between two data sets)?

TIA!

Regards,

[richard_pearce6]

Have you tried "where (not) exists"?

[#3]

I tried this but I'm getting an error.

[FinancialMaster]:

LOAD *

FROM

QVDs\FinancialMaster.qvd (qvd);

[DiffTableYTD]:

NOCONCATENATE

LOAD *

RESIDENT [PremiumYTD]

Where Not Exists([AccountNumber], [ACCOUNT NO]);

But I get an error "Field not found - <ACCOUNT NO>".

[ACCOUNT NO] is loaded in the [FinancialMaster] and [AccountNumber] is loaded in the [PremiumYTD]. So I know the fields exist. the [PremiumYTD] table is loaded prefiously.

The Qlikview reference manual states "The field must exist in the data loaded so far by the script." - this should be met once I have loaded both tables (which I have).

Any ideas?

Thank you

[richard_pearce6 · Accepted solution]

Hi,

Looks like you just got the syntax the wrong way around. I always have trouble remembering the right way to do this.

I've attached an example, I hope this helps

Rich

[#5]

Thanks Rich,

That helped I got it working now.

Thank you for your help.