Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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]
...