Mapping Tables

Wed, 26 Apr 2017 16:01:26 GMT

1) Project: master data of all the below lying information

2) Project - Version: one to many relationship

3) Version - Availability: one to many relationship, for each version one or more resource would work and would have

different available percentage. Some may available for 100%, some may available for 50%, etc.

4) Version - Time sheet: one to many relationship, each resource will log their time spent on each project - version

Assume, we need to calculate actual availability of the hours per resource based on their availability % in Version - Availability data. For example if a resource available 100% we would be saying 5 days a week would sum into 40 hours (5 x 8 = 40).

Now, I want a report to show result by

Project - Version - Resource - Availability - Time logged - Time not entered

Project - from Project table

Version - from Project - Version table

Resource - from Version - Availability table

Availability - Calculated value from Version - Availability table

Time logged - from Version - Timesheet table

Time not entered - difference from Availability - Time logged

The resources may or may not exist in Version - Availability table. Similarly the resource may or may not enter their timesheet entry.

If the resource exist in the Version - Availability table, then we need the calculated Availability value (example 5 x 8 = 40) otherwise 8 hours

If the resource entered timesheet then we need the Time logged hours

We are able to get the report if all the resources exist in the Version - Availability table and entered their timesheet. But, we are unable to match the above two condition when the user is not exist in Version - Availability table but entered timesheet.

Please help me how to map - join the tables and the report

topic Mapping Tables in QlikView

Mapping Tables