How to get the un matched records?

[#1]

Hi All   Can any one help me to get the un matched records in qlikview front end? 

This is my data and i need the difference value records from each table according to position

Table1:

Customer	Contract	position	part	company
1212	101	working	12	Bmw
1215	101	repaired	13	Skoda
1254	102	unused	12	Audy
1254	103	working	13	Bmw
1212	105	repaired	41	Skoda
1215	106	unused	42	Audy
1211	107	working	12	Bmw

Table2:

Customer	Contract	position	part	company	date
1212	101	working	12	Bmw	1/12/2011
1215	101	repaired	13	skoda	2/12/2012
1254	102	unused	12	Audy	4/11/2010
1254	103	working	13	Bmw	3/6/2010
1212	105	repaired	41	skoda	6/4/2009
1215	106	unused	42	Audy	5/4/2008
1211	107	working	12	Bmw	4/9/2012
1211	108	working	12	Bmw	02/5/1/2013
12102	107	working	43	Audy	11/3/1911
1205	109	repaired	44	bmw	12/7/2011
1205	107	unused	46	skoda	11/3/1911
1208	109	working	47	bmw	11/4/1202

Here  is my end what i have upto know

  Position:     Total contract in table1               Total contract in table2        difference

  working              3                                                  6                                 3

here difference is 3 , now i need those 3 different records from table 2.

Hope you understand Please Provide me some help to over come this.   Thank you.

[SunilChauhan]

if you want not matching record with first table

tab1:

Load keyfield,

other fields

from first table

Load keyfield,

other fields

from scond table where not Exists(keyfield,keyfield);

drop table tab1;

if you want not matching record with second table

tab1:

Load keyfield,

other fields

from Second table

Load keyfield,

other fields

from first table where not Exists(keyfield,keyfield);

drop table tab1;

i have dont suggestion now you can implement it

hope this helps

[tresesco]

Could you post a sample qvw? Are these two tables associated?

[SunilChauhan]

ok thats correct

please see the attched file

or

use below code

Tab1:

LOAD Customer&Contract&position as key,

Customer,

    Contract,

    position,

    part,

    company

FROM

C:\Users\Tycoon1\Desktop\Test.xlsx

(ooxml, embedded labels, table is Sheet1) where position='working';

LOAD Customer&Contract&position as key,

Customer,

    Contract,

    position,

    part,

    company,

    date

FROM

C:\Users\Tycoon1\Desktop\Test.xlsx

(ooxml, embedded labels, table is Sheet2) where position='working' and not exists(key,Customer&Contract&position);

only change C:\Users\Tycoon1\Desktop\Test.xlsx to your path

drop table Tab1;

hope this helps

[#5]

Do you only need to count rows from each table on position, or maybe find number of customers existing only in one of those tables?

[#6]

Hi Dariusz Mielczarek

i developed my app upto

i found the count of contracts for each table according to position wise

like

Assume the records are present like this.

Position    count(contracts) in table1    count(contracts) in table2    Difference    More in table1  More in table2       

Working               5                                          4                                1                  1                     0

unused                 6                                          8                                -2                 0                    2

repaired                4                                           1                                3                 3                    0

Now i would like to see the records more in table1   and the records more in table2  

like  if i clik on working position diffrence value 1  , the one record which ever is more in table one it should disply.

I hope its clear to you.

[#7]

But what If you will have for example 2 rows in tab1 and 3 rows in tab 2.

Count difference is 1. But in fact you may have customers with id 1,2 in

tab 1 and customers with id 1,3,5 in tab 2. There are 3 customers (2,3 and

5) present only in one of those tables.

17-05-2014 11:44 użytkownik "johnDawson" <qcwebmaster@qlik.com> napisał:

[#8]

Yes Dariusz what your saying is absolutely correct.  but here my tables maintaining same data.

means

if table1  contains some data . it must be there in Table2. In addition to that some extra data also will be present in table2. but in table1 not like that ,whatever the information available in table1 that information avilable in table2 also.

[#9]

So, why you load both tables? Why not simple load bigger one and only mark

rows present also in 2'nd table...????

17-05-2014 12:14 użytkownik "johnDawson" <qcwebmaster@qlik.com> napisał:

Qlik Community <http://community.qlik.com/> How to get the un

matched records?

reply from johnDawson<http://community.qlik.com/people/johnDawson?et=watches.email.thread>in *New

to QlikView* - View the full discussion<http://community.qlik.com/message/530324?et=watches.email.thread#530324>

[#10]

There is "where exists/ not exists" clauses to do it easy during reload.

17-05-2014 12:14 użytkownik "johnDawson" <qcwebmaster@qlik.com> napisał:

Qlik Community <http://community.qlik.com/> How to get the un

matched records?

reply from johnDawson<http://community.qlik.com/people/johnDawson?et=watches.email.thread>in *New

to QlikView* - View the full discussion<http://community.qlik.com/message/530324?et=watches.email.thread#530324>