4 Replies Latest reply: Mar 4, 2014 1:25 PM by Paul Edrich RSS

    Creating a flag from £'s a Date & Day Of Week

    Paul Edrich

      Thanks for any help on this problem I have as I am completely at a loss.

       

      I want to create a flag 'Compliance' and it would be 'Pass' or 'Fail'.

       

      Table1 has the field flag   'MinimumPosition'  which is 'over' and 'Under'  and Table 2 has a spreadsheet with BranchID, CollectionDay and Frequency - it has no dates.

       

      I have an expression which works fine in a straight table, with some calculated dimensions - the expression I want to emulate in the script is    =If(WeekDay = CollectionDay and MinimumPosition ='Over', 'Fail','Pass').

       

      In the script I have  on a tab after the MasterCalendar :-

       

      tmp:

      Load Distinct

      EODID,

      BranchEodID,

      MinimumPosition

      Resident Table1;

       

      Left Join (Table1)

       

      Load Distinct

      BranchNo as BranchEodID,

      CollectionDay as DowCollection

      Resident Table2:

       

      Drop Table tmp;

       

      Left Join (Table1)

       

      load

      EODID,

      DOWCollection,

      MinimumPosition,

      SummaryDate,

      If(MinimumPosition = 'Over' and DOWCollection = Date(WeekDay(SummaryDate),'DD/MM/YYYY'), 'Fail','Pass') as Compliance_Flag

      Resident (Table1);

       

      I have also tried various combinations of the above with no success and I just cant see where I am going wrong - Removing the ' ' from Over has a Field not found message pop up.

       

      Thanks again for help.

       

      P