8 Replies Latest reply: Feb 18, 2014 12:28 PM by Gustav Gnosspelius RSS

    Join two tables based on condition in the second

      I've got two tables Peopletable and Hourstable like this:

       

      Peopletable

      ProjectPersonProductRole
      AAnnaCarManager
      BAnnaTrainConsultant
      AJohanCarConsultant
      BJohanTrainConsultant

       

      Hourstable

      ProductManagerConsultant
      Car13
      Train47

       

      I'm now constructing a report where you select a person and then see how many hours that person has accumulated per project. So that selecting "Anna" the output would be

       

      Output

      ProjectHours
      A1
      B7

       

      I've got all the formulas working but I haven't found how to merge the tables. In the script I would like to do something like this, but that is not possible.

       

      Peopletable:
      LOAD
      Project,
      Person,
      Product,
      Role
      FROM X;
      
      Hourstable:
      LOAD
      Product,
      Manager,
      Consultant
      FROM Y;
      
      LEFT JOIN (Peopletable)
      LOAD
      Product AS Product
      Manager AS Hours
      RESIDENT Hourstable WHERE Role = 'Manager';
      
      LEFT JOIN (Peopletable)
      LOAD
      Product AS Product
      Consultant AS Hours
      RESIDENT Hourstable WHERE Role = 'Consultant';
      
      
      
      

       

      How do I correctly write this script? Thank you in advance.