topic compare two primary field in QlikView

compare two primary field

userid128223 — Fri, 02 Nov 2012 19:59:32 GMT

I have tables like below

Table1:

Company

ServiceDate

Table2:

Company

WorkDoneDate

how can i create straight table that shows difference between field of company (from table1) to company in table2.

In other words table will show company that exist in one table and does not exist in other and vice versa along with the table name.

Re: compare two primary field

CELAMBARASAN — Fri, 02 Nov 2012 20:07:16 GMT

Use Company as Dimension

Check with expression as if(isNull(ServiceDate) and not isNull(WorkDoneDate),'Exists in Table2',if(isNull(WorkDoneDate) and not isNull(ServiceDate), 'Exists in Table1', 'Exists in Both'))

Because If the company is not exisits in other table then it maps values as null. In other words missing values are considered as Null.

Re: compare two primary field

MayilVahanan — Fri, 02 Nov 2012 20:07:18 GMT

Are you asking like this

Qualify*;

Table1:

Company

ServiceDate;

Table2:

Company

WorkDoneDate;

by using that, you can see the tablename along with company..

are you like this

Table1:

Load company,serviceDate from table1;

Table2:

Load company,workDoneDate from table2 where exists(company,company);

in this, it loads the company in table2 where those company name exists in table1.

in table:

company as dimension:

if( isnull(WorkDoneDate) = -1,'exists in table2', 'exists in table1')

Re: compare two primary field

userid128223 — Fri, 02 Nov 2012 20:27:41 GMT

I need to keep table 1 and table 2 as is with company name common.

However I just want to find bad data quickly by comparing where company exist in one and does not exist in another.

I was thinking of doing a seperate load of two table via outerjoin. and then call them in straight table to show company where it does not match in both tables.

how would i do that.

Re: compare two primary field

MayilVahanan — Fri, 02 Nov 2012 20:35:32 GMT

Try like this

Table1:

Load * from table1;

join(Table1)

Load * from table2;

PFA