Reusing Variables created using As during LOAD

Anonymous — Tue, 24 Oct 2017 16:32:50 GMT

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

rubenmarin — Tue, 24 Oct 2017 17:38:27 GMT

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]

...

topic Reusing Variables created using As during LOAD in QlikView

Reusing Variables created using As during LOAD

Re: Reusing Variables created using As during LOAD