Inner join with multiple columns

[Ankhi]

Hi 

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

[Gysbert_Wassenaar]

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;

[Ankhi · Accepted solution]

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

[Gysbert_Wassenaar · Accepted solution]

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

[Ankhi]

Hi Gysbert,

Thanks for getting back to me .