Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Refrence calculated column in script

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

Tags (3)
1 Solution

Accepted Solutions
Not applicable

Re: Refrence calculated column in script

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.

View solution in original post

7 Replies

Re: Refrence calculated column in script

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

Not applicable

Re: Refrence calculated column in script

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

Not applicable

Re: Refrence calculated column in script

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.

View solution in original post

Not applicable

Re: Refrence calculated column in script

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;

Not applicable

Re: Refrence calculated column in script

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

Not applicable

Re: Refrence calculated column in script

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

MVP & Luminary
MVP & Luminary

Re: Refrence calculated column in script

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;


talk is cheap, supply exceeds demand