3 different Joins on 2 tables

[#1]

Hello All,

I am new to QV and am currently in a learning phase. I am posed with a challenge. I have 2 tables like below, I need to know 1) all the records that are common in both the tables. (Which I can do by Using a Join statement. ) 2) Need to know records  from Customer that are unavailable in Segment and 3) records in Segment not available in Customer.

What are the possible ways of doing that?

Customer:

CustId	CustName	SalesAmt	Discount
1	A	100	10
2	B	200	20
3	C	300	30
4	D	400	40
5	E	500	50
6	F	600	60
7	G	700	70
8	H	800	80
9	I	900	90
10	J	1000	100

and

Segment:

CustId	Category	Segment
8	Retail	MailOrder
9	Wholesale	Traditional
10	Retail	B2B
11	Retail	Traditional
12	Wholesale	MailOrder

[rajni_batra]

You can use outer join with a flag in both the table

Customer:

load

*, 'A' as Table1;

from ABC.qvd;

Outer Join

Segment:

Load

*, 'B' as Table2;

from XYZ.qvd;

load *,

if(isnull(Table1),;Segment Only',

if(isnull(Table2),'Customer Only','Common Ids') as Flag;

resident Customer;

drop table Customer;

hope this helps!!!

[#3]

Thanks Rajni, that one is really helpful. I am however looking at a solution where I would not require to create another column (like Flag as suggested). I am looking at possible ways at joining the table may be by using a Rename functionality. I am okay if I create 3 views or tables on the dashboard. Wanted to check if this can really be achieved without creating synthetic keys? I appologize if I was/am not  clear about my requirements. But yes the suggestion you provided is really great!