Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
No, Qlik will do an outer join using the common fields unless you explicitly specify that you want an inner join.
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;
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
No, Qlik will do an outer join using the common fields unless you explicitly specify that you want an inner join.
Hi Gysbert,
Thanks for getting back to me .