Complex Join Query - Multiple Conditions/Calculations and Dates

parky — Fri, 02 Apr 2021 07:53:11 GMT

Hello Fellow Qlik Users!

I'm hoping one of you more learned community members can help run me through how I would perform a complex join on two tables based on multiple conditions.

I have two separate SQL queries (from the same database table) generating the sample dummy output of the following (or excel attached) 2 tables, which is defined by grouping project milestone names.

Project Lifecycle Table:

Project Number, Project Version, Milestone Name, Milestone Sequence, Start Date, End Date, Total Duration, Milestone Status
123456, 1, Project Creation, 1, 1/01/2019, 20/01/2019, 19, Completed
123456, 1, Customer Invoicing, 6, 20/01/2019, null, 74, WIP
123456, 2, Resource Confirmation, 9, 15/02/2019, 5/03/2019, 18, Completed
123456, 2, Construction, 15, 5/03/2019, null, 30, WIP
987654, 1, Project Creation, 1, 2/01/2019, 7/01/2019, 92, Completed
987654, 1, Customer Invoicing, 4, 7/01/2019, 31/01/2019, 87, Completed
987654, 1, Resource Planning, 7, 31/01/2019, 2/02/2019, 63,Completed
987654, 2, Customer Invoicing, 14, 2/02/2019, null, 61, WIP

Project Hold Table: (where a single or multiple 'Hold' milestone can occur anywhere during Project Lifecycle of a Project Number ie. can be seen as 'milestone hold flags')

Project Number, Hold Type, Milestone Name, Milestone Sequence, Start Date, End Date, Total Duration
123456, External, Customer, 7, 25/01/2019, 10/02/2019, 16
123456, Internal, Engineering, 16, 7/03/2019, 15/03/2019, 8
123456, Internal, Fleet, 17, 8/03/2019, 10/03/2019, 2
987654, External, Customer, 5, 9/01/2019, 11/01/2019, 2
987654, External, Customer, 6, 15/01/2019, 20/01/2019, 5
987654, External, Customer, 15, 5/02/2019, null, 58

I've attempted to use nested/correlated subqueries in SQL (as I don't have access to create temporary tables, or to create a procedure by PL/SQL) however I'm thinking there would be a more optimal way to create an output table in the data load editor of Qlik Sense.

Requirements of Output Table / Join :
- 'Milestone Status' to be calculated on most recent Milestone by Project Number (ie. End Date to be defined as the start of the next milestone by project number)
- Multiple Hold Milestones by Hold Type to be grouped/rolled up by Project Number and looking up the difference of the start/end date by Milestone in the Project Lifecycle
- Output to transpose hold details of completed hold
- Output to transpose hold details of hold in progress (ie. where there is no end date, use system date to calculate date difference as duration)

Required Output Table:

Project Number, Project Version, Milestone Name, Milestone Sequence, Start Date, End Date, Total Duration, Milestone Status, Internal Hold Status, Internal Hold Duration, External Hold Status, External Hold Duration
123456, 1, Project Creation, 1, 1/01/2019, 20/01/2019, 19, Completed, null, null, null, null
123456, 1, Customer Invoicing, 6, 20/01/2019, null, 74, Completed, Completed, 16, null, null
123456, 2, Resource Confirmation, 9, 15/02/2019, 5/03/2019, 18, Completed, null, null, null, null
123456, 2, Construction, 15, 5/03/2019, null, 30, WIP, WIP, 10, null, null
987654, 1, Project Creation, 1, 2/01/2019, 7/01/2019, 92, Completed, null, null, null, null
987654, 1, Customer Invoicing, 4, 7/01/2019, 31/01/2019, 87, Completed, null, null, Completed, 7
987654, 1, Resource Planning, 7, 31/01/2019, 2/02/2019, 63, Completed, null, null, null, null
987654, 2, Customer Invoicing, 14, 2/02/2019, null, 61, WIP, null, null, WIP, 58

The data set I'm working with consists of hundreds of thousands of rows which adds to the challenge 🙂

Thank you all in advance for your help and guidance.

Cheers,

Phil

topic Complex Join Query - Multiple Conditions/Calculations and Dates in App Development

Complex Join Query - Multiple Conditions/Calculations and Dates