topic Re: Join in QlikView

Join

Tue, 18 Mar 2014 11:24:04 GMT

How to show all records which are common in two tables and also records from one table which are not available in another table.

Re: Join

Tue, 18 Mar 2014 11:39:37 GMT

Hi Ankit,

To show records which are common in two tables, use inner join.

If records from one table which are not available in another table then why do u need to join and what basis u need to join. use concatenate load instead if required.

Re: Join

makkemik — Tue, 18 Mar 2014 12:13:23 GMT

Hi,

You have to set one key field with in two table (you can use AS). Then program will connect the fields automatic.

But if you want to join them in one table you can use like:

Table1:

Sql Select RefCode, Name

from Table1;

join (Table1) // You can also use here Left join, right join or outer join

Sql select IDCode as RefCode, Surname

From Table2;

If you dont want join table together, then delete the join line and wite there as TABLE2: program can connect these tables from RefCode and worked with inner join.

I hope it will help

Murat

Re: Join

Tue, 18 Mar 2014 12:13:33 GMT

Hi Lalit,

I am actually new to Qlik view and have been assigned a task which is as follows:

I have got two tables

EMP: Dept

EMPID	EMPNAME	CTC	Bonus	EMPID	DEPTName	Designation
1	A	100	110	8	IT	MD
2	B	200	220	9	BPO	CMD
3	C	300	330	10	BPS	DMD
4	D	400	440	11	IT	MD
5	E	500	550	12	BPO	CMD
6	F	600	660	13	BPS	DMD
7	G	700	770	14	IT	MD
8	H	800	880	15	BPO	CMD
9	I	900	990
10	J	1000	1100
11	K	1100	1210
12	L	1200	1320

and have to do this

Q1. Show all the records which are common to both tables.

Q2. Show all the Records from EMP which are not available in Dept.

Q3. Show all the record from Dept which are not available in EMP.

First one is done by inner join

DEPT:

LOAD EMPID,

DEPTName,

Designation

FROM

(ooxml, embedded labels, table is Sheet1);

Inner Join(DEPT)

EMP:

LOAD EMPID,

EMPNAME,

CTC,

Bonus

FROM

(ooxml, embedded labels, table is EMP);

Now i am not able to show the others in one sheet.

Thanks

Ankit

Re: Join

Tue, 18 Mar 2014 12:18:21 GMT

Hi Murat,

I am actually new to Qlik view and have been assigned a task which is as follows:

I have got two tables

EMP: Dept

EMPID	EMPNAME	CTC	Bonus	EMPID	DEPTName	Designation
1	A	100	110	8	IT	MD
2	B	200	220	9	BPO	CMD
3	C	300	330	10	BPS	DMD
4	D	400	440	11	IT	MD
5	E	500	550	12	BPO	CMD
6	F	600	660	13	BPS	DMD
7	G	700	770	14	IT	MD
8	H	800	880	15	BPO	CMD
9	I	900	990
10	J	1000	1100
11	K	1100	1210
12	L	1200	1320

and have to do this

Q1. Show all the records which are common to both tables.

Q2. Show all the Records from EMP which are not available in Dept.

Q3. Show all the record from Dept which are not available in EMP.

First one is done by inner join

DEPT:

LOAD EMPID,

DEPTName,

Designation

FROM

(ooxml, embedded labels, table is Sheet1);

Inner Join(DEPT)

EMP:

LOAD EMPID,

EMPNAME,

CTC,

Bonus

FROM

(ooxml, embedded labels, table is EMP);

Now i am not able to show the others in one sheet.

Thanks

Ankit

Re: Join

makkemik — Tue, 18 Mar 2014 12:33:55 GMT

Q1:

NewName:

LOAD EMPID,

DEPTName,

Designation

FROM

(ooxml, embedded labels, table is Sheet1);

Inner Join(DEPT)

LOAD EMPID,

EMPNAME,

CTC,

Bonus

FROM

(ooxml, embedded labels, table is EMP);

Q2:

NewName:

LOAD EMPID,

DEPTName,

Designation

FROM

(ooxml, embedded labels, table is Sheet1);

right Join (DEPT)

LOAD EMPID,

EMPNAME,

CTC,

Bonus

FROM

(ooxml, embedded labels, table is EMP);

Q3:

NewName:

LOAD EMPID,

DEPTName,

Designation

FROM

(ooxml, embedded labels, table is Sheet1);

left Join (DEPT)

LOAD EMPID,

EMPNAME,

CTC,

Bonus

FROM

(ooxml, embedded labels, table is EMP);

regards

Murat

Re: Join

Wed, 19 Mar 2014 07:21:49 GMT

Ankit,

This solution will work 100 %.

For Question : 2

Dept:
LOAD EMPID,
DEPTName,
Designation
FROM

(ooxml, embedded labels, table is Sheet1);

EMP:
LOAD EMPID,
     EMPNAME,
     CTC,
     Bonus

FROM

(ooxml, embedded labels, table is EMP)
where not Exists(EMPID);

DROP Table Dept;

-------------------------------------------------------------------------

For Question : 3

EMP:
LOAD EMPID,
     EMPNAME,
     CTC,
     Bonus

FROM

(ooxml, embedded labels, table is EMP);

Dept:
LOAD EMPID,
DEPTName,
Designation
FROM

(ooxml, embedded labels, table is Sheet1)
where not Exists(EMPID);

DROP Table EMP;

you can validate data by taking all fields in straight table.

Try it.

Re: Re: Join

makkemik — Wed, 19 Mar 2014 08:23:52 GMT

Attached file may help you

regards

Re: Join

Wed, 19 Mar 2014 10:11:15 GMT

Hi Murat,

Thanks for the help.

One more thing that can we show these 3 tables in the same dashboard?

Thanks

Ankit

Re: Join

makkemik — Wed, 19 Mar 2014 10:22:18 GMT

Ofcourse...

You cab use container object. first you will create the cahrts than you will add them to container object.

so you can have these three table like group.

Container obj came with QV version 11.

regards

Murat

Re: Join

Thu, 20 Mar 2014 12:04:32 GMT

Thank you very much:)

Re: Join

er_mohit — Thu, 20 Mar 2014 12:13:22 GMT

using where exists function

Re: Join

makkemik — Thu, 20 Mar 2014 13:43:23 GMT

please sign as answered in the forum.

thanks.