topic Re: Joining two tables in QlikView

Joining two tables

Wed, 24 Aug 2011 13:29:29 GMT

Hi All,

TableA:

orgcode	orgname	orgtype	address
1	Tata	MNC	xxx
2	Birla	MNC	yyy
4	jindal	MNC	zzz
6	L&T	MNC	aaa

TableB:

orgcode	orgname	orgtype	address
1	Tata	MNC	xxx
2	Birla	MNC	yyy
3	RMB	MNC	ddd
4	Jindal	MNC	zzz
5	Adani	MNC	ccc
6	L&T	MNC	aaa
7	wipro	MNC	eee

The output should be:

tableA.orgcode	tableB.orgcode	orgname	orgtype	address	matched
1	1	tata	MNC	xxx	matched
2	2	birla	MNC	yyy	matched
--	3	rmb	MNC	ddd	notmatched
4	4	jindal	MNC	zzz	matched
--	5	adani	MNC	ccc	notmatched
6	6	L&T	MNC	aaa	matched
--	7	wipro	MNC	eee	Not matched

Even I have to create a list box called Matched where matched and unmatched option will be there. If I will click on nmatched button it will give only unmatched ones.

Please suggest how to do it...

Thanks

Sandeepa

Joining two tables

its_anandrjs — Wed, 24 Aug 2011 13:58:05 GMT

Hi,

For this two table loads you have to load it conditionally and make a single table and then put on column which check the condition match and Unmatched.

Regards

Anand

Joining two tables

sridhar240784 — Wed, 24 Aug 2011 14:11:31 GMT

To explain bit what Anand has explained. Your code should look like below.

Transaction_Temp:

Load orgcode,

orgcode as orgcode_A,

orgname,

orgtype,

address

From Table A;

Join

Load orgcode,

orgcode as orgcode_B,

orgname,

orgtype,

address

From Table A;

Transaction:

NoConcatenate

Load *,

if(Len(orgcode_A) = 0,'UnMatched','Matched') as Matched_Status

Resident Transaction_Temp;

Drop Table Transaction_Temp;

Above code is an untested code. May need to do tweak if required.

Hope this helps you.

-Sridhar

Joining two tables

its_anandrjs — Wed, 24 Aug 2011 14:20:54 GMT

Hi,

You need to load data from the tables some thing like this as discuss

Tab1:

LOAD orgcode,

orgname,

orgtype,

address

FROM

Data.xlsx

(ooxml, embedded labels, table is Sheet9);

Concatenate(Tab1)

LOAD orgcode2,

orgname,

orgtype,

address,

If(Exists(orgcode,orgcode2),'Matched','Unmatched') as Match

FROM

Data.xlsx

(ooxml, embedded labels, table is Sheet10);

Regards,

Anand

Re: Joining two tables

SunilChauhan — Wed, 24 Aug 2011 15:02:33 GMT

hello dear,

Please see the attached file which will full fill ur requiremnt.

thanks

Sunil Chauhan

Re: Joining two tables

its_anandrjs — Wed, 24 Aug 2011 15:09:16 GMT

Hi,

See the attached sample for clearity it is the sample one.

which uses the code

Tab1:

LOAD orgcode,

orgname,

orgtype,

address

FROM

Data.xlsx

(ooxml, embedded labels, table is Sheet9);

Concatenate(Tab1)

LOAD orgcode2,

orgname,

orgtype,

address,

If(Exists(orgcode,orgcode2),'Matched','Unmatched') as Match

FROM

Data.xlsx

(ooxml, embedded labels, table is Sheet10);

Reagrds,

Anand

Re: Joining two tables

Wed, 24 Aug 2011 20:03:47 GMT

TableA:

orgcode	orgname	Orgtype	address
1	tata	Mnc	aaa
2	birla	Mnc	bbb
4	jindal	Mnc	ccc

TableB:

Personcode	title	firstname	lastname
1	dr	Sandeepa	Rout
2	mr	Debashish	rout
4	dr	Surekha	Nayak

TableC:

Orgcode	Personcode	Primaryaddress
1	1	xx
2	2	yy
4	4	zz

Table1:

Orgcode	Orgname	Orgtypename	Address
1	Tata	Mnc	aaa
2	Birla	Mnc	bbb
3	L&T	Mnc	eee
4	Jindal	Mnc	ccc
5	Adani	Mnc	ddd
6	Wipro	Mnc	fff

Table2:

Personcode	Title	Firstname	Lastname
1	dr	Sandeepa	Rout
2	mr	Debashish	Rout
3	dr	Sandeep	Sahoo
4	dr	Surekha	Nayak
5	dr	Abhimanyu	Rout
6	dr	Laxmipriya	Nayak

Table3:

Orgcode	Personcode	Primaryaddress
1	1	xxx
2	2	yyy
3	3	aaa
4	4	zzz
5	5	bbb
6	6	ccc

Output should be:

Personcode	tableA.orgcode	Table1.orgcode	tableA.orgname	Table1.orgname	matched
1	1	1	Tata	tata	matched
2	2	2	Birla	birla	matched
3	--	3	--	L&T	unmatched
4	4	4	Jindal	jindal	mathced
5	--	5	--	adani	unmatched
6	--	6	--	wipro	unmatched

It means 1st we have to join tableA, tableb, tablec to one(tabA). Then table1, table2, table3 to one (Tab1).

Then we have to compare TabA and Tab1.

But We have to compare orgcode against personcode.

Also we have to create a listbox as Matched where two options should be displayed. If we will click on matched or unmatched it will display accordingly..

Please suggest.

Re: Joining two tables

Thu, 25 Aug 2011 06:32:13 GMT

Please note: the matched field in output table is a label not a predefined field..

Please suggest what to do...