Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Translating a CASE statement from SQL into QV

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.

5 Replies
fosuzuki
Valued Contributor II

Re: Translating a CASE statement from SQL into QV

Can you post the complete select statement?

Not applicable

Re: Translating a CASE statement from SQL into QV

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

Not applicable

Re: Translating a CASE statement from SQL into QV

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

Re: Translating a CASE statement from SQL into QV

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
Valued Contributor II

Re: Translating a CASE statement from SQL into QV

Try changing the CASE syntax to:

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

Community Browser