Reusing Variables created using As during LOAD

[Anonymous]

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.

[rubenmarin]

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]

...