topic Re: How to find difference between two data sets in QlikView

How to find difference between two data sets

Wed, 21 Aug 2013 14:37:11 GMT

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,

Re: How to find difference between two data sets

richard_pearce6 — Wed, 21 Aug 2013 14:43:06 GMT

Have you tried "where (not) exists"?

Re: How to find difference between two data sets

Wed, 21 Aug 2013 15:55:07 GMT

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

Re: Re: How to find difference between two data sets

richard_pearce6 — Wed, 21 Aug 2013 16:08:13 GMT

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

Re: How to find difference between two data sets

Thu, 22 Aug 2013 13:47:27 GMT

Thanks Rich,

That helped I got it working now.

Thank you for your help.