topic Re: Inner join with multiple columns in QlikView

Inner join with multiple columns

Ankhi — Sat, 16 Nov 2024 00:17:22 GMT

I have a query like below where a qvd is created for fact_s as is. Now I want to create another qvd to resolve the below query. Can anyone please help.?

select a.*
from fact_s a
inner join
(select prj_id, task_id, mlstn_id, max(cost + hour) as total_cost
from fact_s group by ora_project_wid, task_wid,keep_milestone_id) b
on a.prj_id = b.prj_id
and a.task_id = b.task_id
and a.mlstn_id = b.mlstn_id
and (a.cost + a.hour) = b.total_cost

Re: Inner join with multiple columns

Gysbert_Wassenaar — Tue, 21 Jul 2020 18:01:33 GMT

Perhaps like this:

LOAD *, cost+hour as total_cost from fact_s.qvd (qvd);

inner join

LOAD ora_project_wid, task_wid,keep_milestone_id, prj_id, task_id, mlstn_id, max(cost + hour) as total_cost
from fact_s.qvd (qvd)
group by ora_project_wid, task_wid,keep_milestone_id, prj_id, taks_id, mlstn_id;

Re: Inner join with multiple columns

Ankhi — Wed, 22 Jul 2020 08:55:59 GMT

Hi Gysbert,

Thanks for replying back. I am trying to code as you have shown.

1 question, regarding the inner join with multiple columns. Is it correct for me to assume that if the column names are same in both the tables on which I want to join like (prj_id, taks_id, mlstn_id exist in both the tables) , qlik will do a inner join considering all these columns like below condition in sql

...

where a.prj_id =b.prj_id and a.taks_id =b.taks_id and a.mlstn_id =b.mlstn_id

Regards

Re: Inner join with multiple columns

Gysbert_Wassenaar — Tue, 04 Aug 2020 13:14:22 GMT

No, Qlik will do an outer join using the common fields unless you explicitly specify that you want an inner join.

Re: Inner join with multiple columns

Ankhi — Thu, 06 Aug 2020 13:19:28 GMT

Hi Gysbert,

Thanks for getting back to me .