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

    Friday afternoon dip, loose tables

      Hello,

       

      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, 
           Periode,
           Periode#weeks
      FROM
      DatesPeriod.xlsx
      (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, 
           @3, 
           @4 as Periode, 
           @5 as FTE
      FROM
      FTE1.xls
      (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:

       

       

      load 
      If (Department = 'PLAYOUT', 'BC PLAYOUT') as Department,
      Datum as Date,
      extern as BillableUren;
      sql 
      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?