1 Reply Latest reply: Oct 24, 2017 1:38 PM by Ruben Marin RSS

    Reusing Variables created using As during LOAD

    Mohammed Abdulmujeeb

      This code works, but there has to be a better way:

       

      If(Status='No Impact', If(Date#([Completion Date])>=1,[Completion Date],[Received Date]),[Completion Date]) as [POP: Completion Date],

       

      The code is using the [Received Date] if the [Completion Date] is missing, but only if the [Status] is "No Impact". So far, there is no problem.

       

      Question #1: Why can't I leave the [Completion Date] as the name of the variable? As you notice, I created a new variable with as [POP: Completion Date]

       

      Next I use the newly created variable in a calculation as follows:

       

      If(If(Status='No Impact', If(Date#([Completion Date])>=1,[Completion Date],[Received Date]),[Completion Date])>0,If(Status='No Impact', If(Date#([Completion Date])>=1,[Completion Date],[Received Date]),[Completion Date])-[Received Date],Today()-[Received Date]) as [POP: Days Open],

       

      I could not use the newly created variable [POP: Completion Date], so I had to cut and paste the entire if statement to accomplish this.

       

      Question #2: Can you reuse the newly created variable in an expression in the same LOAD statement?

       

      Looking forward to some great insights.

        • Re: Reusing Variables created using As during LOAD
          Ruben Marin

          Hi Mohammed:

           

          Question #1: Maybe you have an '*' in the LOAD, or another field that loads with the field (variable) name [Completion Date], and the names should be unique, if there is a field already named [Completion Date] you need to change the name.


          Question #2: there is a 'precending load', wich uses a LOAD as a source for another load, this way you can use calculated fields created in the previous step:


          LOAD *

          If([POP: Completion Date]>0,[POP: Completion Date]-[Received Date],Today()-[Received Date]) as [POP: Days Open]

          ;

          LOAD *,

               If(Status='No Impact', If(Date#([Completion Date])>=1,[Completion Date],[Received Date]),[Completion Date]) as [POP: Completion Date]

          ...