4 Replies Latest reply: Aug 8, 2011 10:42 AM by Daniel Garguilo RSS

    Flag field based on another table

      Hi All

       

      I would like to add a field to Table A based on the fourmula (IF(WeekDayNUmber = 3, 1,2), However WeekDayNumber is a field in Table B. Any advice on hwo best to go about thsi woudl be great. Please see the atatched test document.

       

      Thanks

       

      Dan

        • Flag field based on another table
          Sunil Chauhan

          A:
          LOAD DateActual
          FROM
          Test.xls
          (biff, embedded labels, table is A$);

           

           

          B:
          LOAD DateActual,
               WeekdayNumber,
               DayOfMonthNumber,
               CYDayOfYearNumber,
               CYWeekOfYearNumber,
               CYMonthNumber,
               [CY Quarter],
               [CY Year],
               [CY Year Month],
               [CY Year Month Name],
               [CY Year Month Short],
               [CY Year Quarter],
               FYDayOfYearNumber,
               FYWeekOfYearNumber,
               FYMonthNumber,
               [FY Quarter],
               [FY Year],
               [FY Year Month],
               [FY Year Month Name],
               [FY Year Month Short],
               [FY Year Quarter],
               [Out of Hours]
          FROM
          Test.xls
          (biff, embedded labels, table is B$);


          left Join(A)

          Load DateActual,
          WeekdayNumber as WeekdayNumber1
          resident B;

          C:
          Load
          DateActual

          if(WeekdayNumber1=3,1,2) as Flag
          resident A;

          drop table A;