4 Replies Latest reply: Aug 15, 2012 2:38 PM by Liz Bauer RSS

    If statements with Dates

    Liz Bauer

      I would like to create an expression.

      (not sure whether to place in the Script or in a Chart)

       

      But I have 2 fields Titled

      [Planned Date]-- list of dates

      [Actual Date]-- list of dates

       

      Also I created a reference field:

      STATUS        DAYS_STATUS

      Early                      +1

      Late                        -1

      On Time                   0

      Scheduled                0

       

       

       

      If the Planned Date is before the Actual Date, The Status should be 'On Time' with a DAYS_STATUS of 0.

      If the Actual Date is before the Planned Date, The Status is Early with DAYS_STATUS of 1.

      If the Planned Date is before  Today and the Actual Date is blank/ null, the Stutus should be 'Late' with a DAYS_STATUS of -1

      If The Planned Date is later than Today's date, the Status should be Scheduled with a DAYS_STATUS of 0.

       

      So far.. I've come up with this type of script.. but it's not working and I'm not sure what to do..

       

      If [date([Planned Date]) < date([Actual Date]), then  STATUS= 'On Time' and DAYS_STATUS = 0.

       

       

      Ideally, What I'd like to create is a table which shows the Order Number, the STATUS to see which parts are on time, early, late & still scheduled

      and to make a count of the DAYS_STATUS to see how behind production is.

       

      Any Suggestions would be very appreciated!

       

      --Please Write the Script in the Comment instead of attaching files 

        • Re: If statements with Dates
          Manoj Kumar Varatharajan

          Please add these two statements to your Load script to generate the table fields STATUS and DAYS_STATUS. I hope it meets your requirements.

           

          IF ([Planned Date] < [Actual Date], 'On Time',

          IF ([Planned Date] > [Actual Date], 'Early',

          IF ([Planned Date] < today() AND isnull([Actual Date]) = -1, 'Late',

          IF ([Planned Date] > today(), 'Scheduled')))) AS STATUS,

           

          IF ([Planned Date] < [Actual Date], 0,

          IF ([Planned Date] > [Actual Date], 1,

          IF ([Planned Date] < today() AND isnull([Actual Date]) = -1, -1,

          IF ([Planned Date] > today(), 0)))) AS DAYS_STATUS,

            • Re: If statements with Dates
              Liz Bauer

              The only issue I'm having is that

              Planned date is pulling from TORDFD.txt, while Actual Date is pulling form TORDOP.txt..

               

              So I've tried putting the statement in the 2nd load statement of the Actual Date,

              But when I tried running it, it had an error saying that it couldn't recognize the Planned Date..

               

               

              Any suggestions on where I should load the data?

               

              Thank you!!!

            • Re: If statements with Dates
              Manoj Kumar Varatharajan

              There are lot of work arounds to it. However, I would begin with the following logic. Please let me know if it helps or upload a sample datasource file and let me look at it.

               

              1. Create a table named Table_Temp that would join TORDFD.txt and TORDOP.txt data with the common field (as Key).
              2. Create a Table_Actual using the RESIDENT LOAD incorporating the IF logic
              3. Drop the Table_Temp

                     

              Sample script:

               

                   Table_Temp:

               

                   Load Key,

                   *

                   FROM TORDFD.txt;

                  

                   JOIN

               

                   Load Key,

                   *

                   FROM TORDOP.txt;

                  

                   Table_Actual:

                  

                   LOAD *,

                  

              IF ([Planned Date] < [Actual Date], 'On Time',

              IF ([Planned Date] > [Actual Date], 'Early',

              IF ([Planned Date] < today() AND isnull([Actual Date]) = -1, 'Late',

              IF ([Planned Date] > today(), 'Scheduled')))) AS STATUS,

              IF ([Planned Date] < [Actual Date], 0,

              IF ([Planned Date] > [Actual Date], 1,

              IF ([Planned Date] < today() AND isnull([Actual Date]) = -1, -1,

              IF ([Planned Date] > today(), 0)))) AS DAYS_STATUS

               

                  

               

                   RESIDENT Table_Temp;

               

              DROP TABLE Table_Temp;