Refrence calculated column in script

[#1]

I am trying to reference a field that i have calculated in my Qlikview script.

When i try to use this field i get error stating "Field not found". I assume this is because internally qlikview has not yet generated the field. It may get pretty cluttered if i am to use the formulas instead of a refrence to the column name.

As you can see below i am trying to reuse the column called DueDate to see if the SLA is broken.

This is the load statement:

IF(Hour(FileSent) <= 3,

  Timestamp(FileSent+SLADays),

  Timestamp(FileSent+SLADays+1)) AS DueDate,

  IF(Len(Delivered) >0,

  IF(Delivered<=DueDate,

  'OK',

  'DELAYED'

  ),

  IF(Now()<=DueDate,

  'OK',

  'DELAYED'

  )

  ) AS SLAStatus

[alexandros17]

Unfortunately you cannot use your alis name inside the same query, to perform the comparison you have to repeat the code so:

IF(Hour(FileSent) <= 3,

  Timestamp(FileSent+SLADays),

  Timestamp(FileSent+SLADays+1)) AS DueDate,

  IF(Len(Delivered) >0,

  IF(Delivered<=

IF(Hour(FileSent) <= 3,

  Timestamp(FileSent+SLADays),

  Timestamp(FileSent+SLADays+1))

,

  'OK',

  'DELAYED'

  ),

  IF(Now()<=

IF(Hour(FileSent) <= 3,

  Timestamp(FileSent+SLADays),

  Timestamp(FileSent+SLADays+1))

,

  'OK',

  'DELAYED'

  )

  ) AS SLAStatus

Hope it helps

[#3]

That was what i assumed, but hoped not was the case.

[#4 · Accepted solution]

Hi Lasse,

At load time you cannot use any column names as they are not available here.

Unfortunataly you will have to use the actual expressions, however you can put this into a variable and then reference the variable.

For instance add the expression used to calculate the DueDate column into a variable called var.

Then reference the variable in your script instead.

[#5]

If i do this, the variable "varDueDate" is null.

LET varDueDate=IF(Hour(FileSent) <= 3,Timestamp(FileSent+SLADays),Timestamp(FileSent+SLADays+1));

LOAD

IF(Len(Delivered) >0,

       IF(Delivered<=$(varDueDate),

            'OK',

            'DELAYED'

       ),

       IF(Now()<=$(varDueDate),

            'OK',

            'DELAYED'

       )

  ) AS SLAStatus;

[#6]

By using SET instead of LET it seemed to work. I diden't know there was to different ways to create variables.

[#7]

Yeah. When using SET you want set a variable with a "fixed" string. When using LET you want the variable to calculate first before setting it.

Hope this helps.

Regards,
Marius

[Gysbert_Wassenaar]

Use a preceding load:

MyTable:

LOAD * ,

  IF(Len(Delivered) >0,

  IF(Delivered<=DueDate,

  'OK',

  'DELAYED'

  ),

  IF(Now()<=DueDate,

  'OK',

  'DELAYED'

  )

  ) AS SLAStatus

LOAD *,

IF(Hour(FileSent) <= 3,

  Timestamp(FileSent+SLADays),

  Timestamp(FileSent+SLADays+1)) AS DueDate

FROM ... etc;