topic Re: How to join more than one column? in QlikView

How to join more than one column?

Tue, 12 Mar 2013 13:56:34 GMT

Hi friends,

I have the following query in sql syntax:

select stu.name,stu.regd_id,attd.address,mks.total from student stu,marks mks,attendance attd where stu.regd_id=mks.regd_id and stu.regd_id=attd.regd_id and attd.serial in (marks.number,12,76,89);

My Intention is--- To display the student details with the "student.ID" in marks and attendance tables and 'attd.serial' in either marks table or '12,76,89'

Note: Student.ID will have multiple entries in attendance table and I need the result with the selected attd.serial.

How to convert this SQL query to qlikview script?

I have written this with the QV Community help

load name,

regd_id as id,

FROM

(qvd);

Inner Join

load

regd_id as id,

number as numb

FROM

(qvd);

Inner Join

load regd_id as id

serial,

address,

FROM

(qvd)where exists(numb,serial) or match(serial,12,76,89);

--But I got the result set with all the student ID's which not desired.

Please help, Any help is greatly appreciated.

Re: How to join more than one column?

sushil353 — Tue, 12 Mar 2013 14:03:01 GMT

can u post the sample data?

Re: How to join more than one column?

Tue, 12 Mar 2013 14:45:15 GMT

Hi Sushil,

Existing sample set is

Student Marks Attendance

Regd_id Name Regd_id Number Regd_id Serial Address

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

1001 arun 1001 301 1001 12 xyz

1002 paul 1001 201 abc

1001 301 pqr

I want the result-set as

Regd_id Name Serial Address

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

1001 arun 12 xyz

1001 arun 301 pqr

Serial No. '12' because i want it explicitly and '301' because it exists in "Marks" table.

Re: How to join more than one column?

VishalWaghole — Wed, 13 Mar 2013 13:37:45 GMT

Hi,

Please try this one.

ATTD:
load       regd_id as id,
              serial,
              address,
FROM

(qvd)where exists(numb,serial) or match(serial,12,76,89);

left Join (ATTD)
load name,
regd_id as id,
FROM

(qvd);

left Join (ATTD)
load regd_id as id,
number as numb
FROM

(qvd);

If still have doubt then share your qvd's data with some dummy Data.

Thanks and Regards,

Vishal Waghole

Re: How to join more than one column?

Thu, 14 Mar 2013 10:32:19 GMT

Thanks Vishal,

Logic worked well.