Translating a CASE statement from SQL into QV

Fri, 14 Dec 2012 14:13:28 GMT

Hello. So I'm trying to convert a SQL script that I know works outside of QlikView, but I'm not understanding how to bring in the CASE statement that sits in the FROM part of the script. I keep getting syntax errors on reload. Some have suggested mapping but I really don't think I want it for this instance.

Within the FROM portion of the SQL script a variable called 'Plan_Status' is created like below..

FROM

CPR.CASPR.Active_Project_Attributes INNER JOIN (

SELECT PROJ_NO, cpr_program,

'Plan_STATUS' =

CASE

WHEN (TRIM_Actual_Transport_Delivery_Date IS NULL) AND (TRIM_PlanOnAir_Target_Date >= CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) THEN 'Grey'

I also don't think I need the inner join to pull what I want, but I do need that 'Plan_Status' variable field to be Red, Yellow, or Grey depending on the logic above.

Re: Translating a CASE statement from SQL into QV

fosuzuki — Fri, 14 Dec 2012 19:58:00 GMT

Can you post the complete select statement?

Re: Translating a CASE statement from SQL into QV

Fri, 14 Dec 2012 20:01:41 GMT

It's ugly but ok.

SELECT

CPR.CASPR.Active_Project_Attributes.CPR_Region,

CPR.CASPR.Active_Project_Attributes.CPR_Market,

CPR.CASPR.Active_Project_Attributes.CPR_Program,

CPR.CASPR.Active_Project_Attributes.PROJ_NO,

CPR.CASPR.Active_Project_Attributes.TRIM_Plan_On_Air_Date,

CPR.CASPR.Active_Project_Attributes.PLAN149,

CPR.CASPR.Active_Project_Attributes.PLAN150,

CPR.CASPR.Active_Project_Attributes.ACTUAL099,

CPR.CASPR.Active_Project_Attributes.ACTUAL100,

count(CPR.CASPR.Active_Project_Attributes.PROJ_NO),

CPR.CASPR.Active_Project_Attributes.TRIM_Actual_Transport_Delivery_Date,

CPR.CASPR.Active_Project_Attributes.TRIM_PlanOnAir_Target_Date,

TransHealth_Status_DT.FCST_STATUS,

CPR.CASPR.Active_Project_Attributes.TRIM_30days_before_PlanOnAir_Date,

TransHealth_Status_DT.Fcst_Project_Due_Status,

CPR.CASPR.Active_Project_Attributes.TRIM_FcstOnAir_Target_Date,

CPR.CASPR.Active_Project_Attributes.TRIM_Fcst_On_Air_Date

FROM

CPR.CASPR.Active_Project_Attributes INNER JOIN (

SELECT PROJ_NO, cpr_program,

'Plan_STATUS' =

CASE

WHEN (TRIM_Actual_Transport_Delivery_Date IS NULL) AND (TRIM_PlanOnAir_Target_Date >= CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) THEN 'Grey'

WHEN (TRIM_Actual_Transport_Delivery_Date IS NOT NULL) and (TRIM_Actual_Transport_Delivery_Date <= TRIM_PlanOnAir_Target_Date)THEN 'Green'

WHEN (TRIM_Actual_Transport_Delivery_Date IS NOT NULL) and ((TRIM_Actual_Transport_Delivery_Date >= TRIM_PlanOnAir_Target_Date) AND (TRIM_Actual_Transport_Delivery_Date <= TRIM_30days_before_PlanOnAir_Date)) THEN 'Yellow'

WHEN (TRIM_Actual_Transport_Delivery_Date is NULL) and (TRIM_PlanOnAir_Target_Date < CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) THEN 'Red'

WHEN (TRIM_Actual_Transport_Delivery_Date IS NOT NULL) and (TRIM_Actual_Transport_Delivery_Date > TRIM_30days_before_PlanOnAir_Date) THEN 'Red'

ELSE 'Other'

END,

'Tranport_Status' =

CASE

WHEN (TRIM_Actual_Transport_Delivery_Date IS NOT NULL) THEN 'Transport Delivered'

WHEN ((CASPR.Active_Project_Attributes.TRIM_Actual_Submit_Order_Date IS NULL) And (TRIM_Actual_Transport_Delivery_Date IS NULL)) THEN 'Transport not Ord'

WHEN ((CASPR.Active_Project_Attributes.TRIM_Actual_Submit_Order_Date IS NOT NULL) And (TRIM_Actual_Transport_Delivery_Date IS NULL)) THEN 'Transport Ord'

ELSE 'Other'

END,

'Plan_Project_Due_Status' =

CASE

WHEN ((TRIM_Actual_Transport_Delivery_Date IS NULL) AND ( CASPR.Active_Project_Attributes.TRIM_PlanOnAir_Target_Date - CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)>60)) THEN 'Due after 60 days'

WHEN (((TRIM_Actual_Transport_Delivery_Date IS NULL)) And ( CASPR.Active_Project_Attributes.TRIM_PlanOnAir_Target_Date - CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)<0)) THEN 'Past Due'

WHEN ((TRIM_Actual_Transport_Delivery_Date IS NULL) AND ( CASPR.Active_Project_Attributes.TRIM_PlanOnAir_Target_Date - CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)<=60)) THEN 'Due next 60 days'

ELSE 'Other'

END

,'FCST_STATUS' =

CASE

WHEN (TRIM_Actual_Transport_Delivery_Date IS NULL) AND (TRIM_FcstOnAir_Target_Date >= CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) THEN 'Grey'

WHEN (TRIM_Actual_Transport_Delivery_Date IS NOT NULL) and (TRIM_Actual_Transport_Delivery_Date <= TRIM_FcstOnAir_Target_Date)THEN 'Green'

WHEN (TRIM_Actual_Transport_Delivery_Date IS NOT NULL) and ((TRIM_Actual_Transport_Delivery_Date >= TRIM_FcstOnAir_Target_Date) AND (TRIM_Actual_Transport_Delivery_Date <= (TRIM_Fcst_On_Air_Date-30))) THEN 'Yellow'

WHEN (TRIM_Actual_Transport_Delivery_Date is NULL) and (TRIM_FcstOnAir_Target_Date < CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) THEN 'Red'

WHEN (TRIM_Actual_Transport_Delivery_Date IS NOT NULL) and (TRIM_Actual_Transport_Delivery_Date > (TRIM_Fcst_On_Air_Date-30)) THEN 'Red'

ELSE 'Other'

END

,'Fcst_Project_Due_Status' =

CASE

WHEN ((TRIM_Actual_Transport_Delivery_Date IS NULL) AND ( CASPR.Active_Project_Attributes.TRIM_FcstOnAir_Target_Date - CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)>60)) THEN 'Due after 60 days'

WHEN (((TRIM_Actual_Transport_Delivery_Date IS NULL)) And ( CASPR.Active_Project_Attributes.TRIM_FcstOnAir_Target_Date - CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)<0)) THEN 'Past Due'

WHEN ((TRIM_Actual_Transport_Delivery_Date IS NULL) AND ( CASPR.Active_Project_Attributes.TRIM_FcstOnAir_Target_Date - CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)<=60)) THEN 'Due next 60 days'

ELSE 'Other'

END

FROM CPR.CASPR.Active_Project_Attributes

) TransHealth_Status_DT ON (CPR.CASPR.Active_Project_Attributes.PROJ_NO=TransHealth_Status_DT.PROJ_NO)

WHERE

(

CPR.CASPR.Active_Project_Attributes.CPR_Program IN ( '1st Carrier','2nd Carrier','3rd Carrier','4th Carrier','5th Carrier','6th Carrier','LTE','NSB' )

AND

CPR.CASPR.Active_Project_Attributes.TRIM_Fcst_On_Air_Date BETWEEN '01/01/2012 00:0:0' AND '08/16/2012 00:0:0'

)

GROUP BY

CPR.CASPR.Active_Project_Attributes.CPR_Region,

CPR.CASPR.Active_Project_Attributes.CPR_Market,

CPR.CASPR.Active_Project_Attributes.CPR_Program,

CPR.CASPR.Active_Project_Attributes.PROJ_NO,

CPR.CASPR.Active_Project_Attributes.TRIM_Plan_On_Air_Date,

CPR.CASPR.Active_Project_Attributes.PLAN149,

CPR.CASPR.Active_Project_Attributes.PLAN150,

CPR.CASPR.Active_Project_Attributes.ACTUAL099,

CPR.CASPR.Active_Project_Attributes.ACTUAL100,

CPR.CASPR.Active_Project_Attributes.TRIM_Actual_Transport_Delivery_Date,

CPR.CASPR.Active_Project_Attributes.TRIM_PlanOnAir_Target_Date,

TransHealth_Status_DT.FCST_STATUS,

CPR.CASPR.Active_Project_Attributes.TRIM_30days_before_PlanOnAir_Date,

TransHealth_Status_DT.Fcst_Project_Due_Status,

CPR.CASPR.Active_Project_Attributes.TRIM_FcstOnAir_Target_Date,

CPR.CASPR.Active_Project_Attributes.TRIM_Fcst_On_Air_Date

Re: Translating a CASE statement from SQL into QV

Mon, 17 Dec 2012 13:19:37 GMT

I get numerous syntax errors when moving a SQL statement over to QV.

Re: Translating a CASE statement from SQL into QV

Miguel_Angel_Baeyens — Mon, 17 Dec 2012 13:23:01 GMT

Hi,

Are you using ODBC or OLE DB? Which type of errors are you getting? Everything after the SQL or SELECT statement goes directly into the driver, then sent to the database. So there may be a syntax issue (i. e.: not accepted keyword) in either the driver or the RDBMS, as long as QlikView does not do anything in that part.

Hope that helps.

Miguel

Re: Translating a CASE statement from SQL into QV

fosuzuki — Mon, 17 Dec 2012 13:47:26 GMT

Try changing the CASE syntax to:

SELECT CASE WHEN [Condition] THEN [ThenStatement] ELSE [ElseStatement] END AS "[FieldName]"

topic Re: Translating a CASE statement from SQL into QV in QlikView

Translating a CASE statement from SQL into QV

Re: Translating a CASE statement from SQL into QV

Re: Translating a CASE statement from SQL into QV

Re: Translating a CASE statement from SQL into QV

Re: Translating a CASE statement from SQL into QV

Re: Translating a CASE statement from SQL into QV