Translating a CASE statement from SQL into QV

[#1]

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.

[fosuzuki]

Can you post the complete select statement?

[#3]

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

[#4]

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

[Miguel_Angel_Baeyens]

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

[fosuzuki]

Try changing the CASE syntax to:

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