Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
That was what i assumed, but hoped not was the case.
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.
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;
By using SET instead of LET it seemed to work. I diden't know there was to different ways to create variables.
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
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;