topic Re: Refrence calculated column in script in QlikView

Refrence calculated column in script

Tue, 01 Jul 2014 13:33:16 GMT

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

Re: Refrence calculated column in script

alexandros17 — Tue, 01 Jul 2014 13:36:01 GMT

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

Re: Refrence calculated column in script

Tue, 01 Jul 2014 13:37:24 GMT

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

Re: Refrence calculated column in script

Tue, 01 Jul 2014 13:41:45 GMT

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.

Re: Refrence calculated column in script

Tue, 01 Jul 2014 13:50:09 GMT

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;

Re: Refrence calculated column in script

Tue, 01 Jul 2014 14:09:44 GMT

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

Re: Refrence calculated column in script

Tue, 01 Jul 2014 14:12:40 GMT

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

Re: Refrence calculated column in script

Gysbert_Wassenaar — Tue, 01 Jul 2014 14:23:58 GMT

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;