18 Replies Latest reply: May 25, 2011 6:55 AM by Erica de+Boer RSS

    Friday afternoon dip, loose tables



      Maybe it's because it's almost weekend and I'm tired, but I don't know how to solve this.


      I have created a mastercalendar because I need Periods instead of months.

      So for this, I created an Excel file which has all the possible dates, the periodnumber en the number of weeks of that period.


      So that means:



      LOAD Datum as Date, 
      (ooxml, embedded labels, table is Blad1);


      Also I have an Excel file which shows the number of FTE for each department in the same period.




      LOAD @1 as Depnr, 
           @2 as Department, 
           @4 as Periode, 
           @5 as FTE
      (biff, no labels, table is Blad1$);

      Next I need all the bookedhours on their respective bookingdates, and the idea is that with the Posting Date as Date, I automatically get the right Period. But on Department. For example:



      If (Department = 'PLAYOUT', 'BC PLAYOUT') as Department,
      Datum as Date,
      extern as BillableUren;
      select resg."Responsibility Center" as Department,
      led."Posting Date" as Datum,
      sum (led."Quantity") as extern
      From Table X


      As you may guess, I get a loop here and then some loosely coupled tables.


      How can I make this work without a loop?