1 Reply Latest reply: Nov 29, 2010 9:27 AM by JurajChovan RSS

    How to bind 2 tables data and sum from third table

      Hi friends,
      I have data source in following format:

      table "ProjectData":
      IDMyProject MyProjectName
      1 project 01
      1 project 02
      ...

      table "DeliverableData":
      IDMyProject IDMyDeliverable IDMyProjectDeliverable MyDeliverableName MyDeliverableStartDate MyDeliverableEndDate
      1 1 1 deliverable P01-01 1.1.2010 31.1.2010
      1 2 2 deliverable P01-02 15.1.2010 15.2.2010
      ...

      table "DeliverableRealWorkingActivity":
      IDMyProject IDMyDeliverable IDMyProjectDeliverable IDWorkingJob WorkingDate DurationOfWorking MyDeliverableSource
      1 1 1 1 1.1.2010 45 JC
      1 1 1 2 5.1.2010 60 JC
      1 1 1 3 3.1.2010 100 AB
      1 1 1 4 4.1.2010 120 AB
      ...

      I would like to build following data:
      IDMyProject MyProjectName IDMyDeliverable IDMyProjectDeliverable MyDeliverableName MyDeliverableStartDate MyDeliverableEndDate SumOfWorkingDurationForAllSources
      1 project 01 1 1 deliverable P01-01 1.1.2010 31.1.2010 325
      1 project 01 2 2 deliverable P01-02 15.1.2010 15.2.2010 ...
      ...

      where last column "SumOfWorkingDurationForAllSources" would be calculated sum of all rows (values "DurationOfWorking") of table "DeliverableRealWorkingActivity" for each "IDMyProject" and "IDMyDeliverable".

      Does anybody have some idea, how can I write script to load these data and create mentioned structure of data?

      very thanks

        • How to bind 2 tables data and sum from third table

          hi friends,

          voila! I find solution! Loading script may be following:

          TempCostsProjectData:
          LOAD IDMyProject,
          MyProjectName
          FROM Test.xls (biff, embedded labels, table is ProjectData$);
          JOIN LOAD
          IDMyProject,
          IDMyDeliverable,
          IDMyProjectDeliverable,
          MyDeliverableName,
          MyDeliverableStartDate,
          MyDeliverableEndDate
          FROM Test.xls (biff, embedded labels, table is DeliverableData$);
          JOIN LOAD
          IDMyProject,
          IDMyDeliverable,
          IDMyProjectDeliverable,
          Sum(DurationOfWorking) AS SumDurationOfWorking
          FROM Test.xls (biff, embedded labels, table is DeliverableRealWorkingActivity$)
          GROUP BY IDMyProject, IDMyDeliverable, IDMyProjectDeliverable;

          CostsProjectData:
          LOAD IDMyProject,
          MyProjectName,
          IDMyDeliverable,
          IDMyProjectDeliverable,
          MyDeliverableName,
          MyDeliverableStartDate,
          MyDeliverableEndDate,
          SumDurationOfWorking
          Resident TempCostsProjectData;