Help needed: Multi Field Formula-db

[msbs48]

Using Formula-In_DB

trying to create an additional output field based on reading a table

I have it here as an nested IF because logically it is easier to read... but for Alteryx it will need to be a CASE statement as i do not think it is possible to do the IF in a  Formula-db control.  as Altreryx is adding the code added in Formula-in-DB to bottom of the select query in the Connect_in_DB tool .

Even if i can break the code into 2 sets of Cases...the 1st case ends at the Else in the outer most IF.    if i can do that i can then probably i could  add a 2nd Formua-in-DB to create another field then then evaluate the contents of Field A  - result of 1st Case vs result of Field B - result of 2nd Case

ANY HELP WOULD BE APPRECIATED

BEN

IF [Request Type Consolidated] = 'Estimate' THEN
     IF ISNULL([Estimate Provided Date]) THEN
          IF ISNULL([Cancelled Date]) THEN
                       [Original End SLA]
            ELSE
                     [Cancelled Date]
           END
   ELSE
         [Estimate Provided Date]
    END
ELSE
            IF ISNULL([Active Date]) THEN
                IF ISNULL([Cancelled Date]) THEN
                   [Original End SLA]
               ELSE
                   [Cancelled Date]
               END
         ELSE
            [Active Date]
          END
END

[MarqueeCrew]

CASE OUTPUT_FIELD 
WHEN "Request Type Consolidated" = 'Estimate' AND
            "Estimate Provided Date" IS NULL AND
            "Cancelled Date" IS NULL
            THEN "Original End SLA"
WHEN "Request Type Consolidated" = 'Estimate' AND
            "Estimate Provided Date" IS NULL AND
            "Cancelled Date" IS NOT NULL
            THEN "Cancelled Date"
WHEN "Request Type Consolidated" = 'Estimate' AND
            "Estimate Provided Date" IS NOT NULL
            THEN "Estimate Provided Date"
WHEN "Active Date" IS NULL AND
            "Cancelled Date" IS NULL
            THEN "Original End SLA"
WHEN "Active Date" IS NULL AND
            "Cancelled Date" IS NOT NULL
            THEN "Cancelled Date"
ELSE   "Active Date"
END

Try this or something like it.....

Cheers,

Mark

[msbs48]

Mark...

thanks for you help.. logically what you detailed is much better approach   HOWEVER

I TRIED  entering just small part of the logic and got a ORA-00905: missing keyword

Case "Create_Date"
           When "Request_Type_Consolidated" = 'Estimate'
                     AND "ESTM_PRVD_DTTM" IS NULL
                     AND "CNCLD_DTTM" IS NULL
                    THEN "Original_End_SLA"
                   ELSE "Active_date"
END

here is the full error

Error: Formula In-DB (8): DataWrapOCI: Unable to prepare the query: "WITH "Tool2_73d7" AS (select * from eit_ext.pssc_all_request), "Tool4_2323" AS (SELECT "SRC_SYS_ID", "CUR_ESTN_STS" AS "Status", "CRT_DTTM" AS "Create_Date", "RQST_SUBMT_DTTM" AS "Request_Submitted_date", "ACPT_DTTM" AS "Accepted_date", "ACT_DTTM" AS "Active_date", "ESTM_PRVD_DTTM", "CNCLD_DTTM", "ORDR_OWN" AS "Order_Owner", "ORDR_OWN_EML" AS "Order_Owner_Email", "ORDR_ANLST" AS "Order_Analyst", "ORDR_ANLST_EML" AS "Order_Analyst_Email", "RQST_SPNSRG_RGN" AS "Sponsoring_Region", "ORDR_OWN_MGR" AS "Order_Owner_MGR", "ORDR_OWN_MGR_EML" AS "Order_Owner__MGR_Email", "ORDR_OWN_VP" AS "Order_Owner_VP", "ORDR_OWN_VP_EML" AS "Order_Owner_VP_Email", "RQST_TYPE" AS "Request_Type", "DOMNT_SRVC" AS "Dominant_Services", "CYC_AGE" AS "Cycle_Age", "PROJ_STRT_DT" AS "Project_Start_Date", "PROJ_END_DT" AS "Project_End_Date", "CYC_NUM" AS "Av", "PROJ_NM" AS "Order_Description", "QUOTE_INIT_DTTM" AS "Quote_Initiate_Date", "SOW_NUM" AS "Sow_Number", "TIER_NM" AS "Tier", "INSTANTIS_ID" AS "Instantis_ID", "SUBMT_APPR_DTTM" AS "Submitted_For_Approval_date", "BUSN_WAIT_TIME" AS "Total_Buit_Time", "BUSN_APPR_TIME" AS "Business_Approval_Time", "AS_POC_TIME" AS "Total_AS_POC_Time", "PSSC_TIME" AS "Total_Pssc_Time", "END_TO_END_TIME" AS "Total_End_To_End_time", "AS_APPR_TIME" AS "Total_AS_Approval_Time", "QSTR_WAIT_TIME" AS "Questionnaire_Waiting_time", "ORIG_END_SLA" AS "Original_End_SLA", "SRVC_NM" AS "Service", "OFR_NM" AS "Offering" FROM "Tool2_73d7"), "Tool5_3fa9" AS (SELECT "SRC_SYS_ID", "Status", "Create_Date", "Request_Submitted_date", "Accepted_date", "Active_date", "ESTM_PRVD_DTTM", "CNCLD_DTTM", "Order_Owner", "Order_Owner_Email", "Order_Analyst", "Order_Analyst_Email", "Sponsoring_Region", "Order_Owner_MGR", "Order_Owner__MGR_Email", "Order_Owner_VP", "Order_Owner_VP_Email", "Request_Type", "Dominant_Services", "Cycle_Age", "Project_Start_Date", "Project_End_Date", "Av", "Order_Description", "Quote_Initiate_Date", "Sow_Number", "Tier", "Instantis_ID", "Submitted_For_Approval_date", "Total_Buit_Time", "Business_Approval_Time", "Total_AS_POC_Time", "Total_Pssc_Time", "Total_End_To_End_time", "Total_AS_Approval_Time", "Questionnaire_Waiting_time", "Original_End_SLA", "Service", "Offering", (CASE WHEN "Request_Type" = 'Quote for CAR' then 'Quote' ELSE "Request_Type" END ) AS "Request_Type_Consolidated" FROM "Tool4_2323") SELECT "SRC_SYS_ID", "Status", "Create_Date", "Request_Submitted_date", "Accepted_date", "Active_date", "ESTM_PRVD_DTTM", "CNCLD_DTTM", "Order_Owner", "Order_Owner_Email", "Order_Analyst", "Order_Analyst_Email", "Sponsoring_Region", "Order_Owner_MGR", "Order_Owner__MGR_Email", "Order_Owner_VP", "Order_Owner_VP_Email", "Request_Type", "Dominant_Services", "Cycle_Age", "Project_Start_Date", "Project_End_Date", "Av", "Order_Description", "Quote_Initiate_Date", "Sow_Number", "Tier", "Instantis_ID", "Submitted_For_Approval_date", "Total_Buit_Time", "Business_Approval_Time", "Total_AS_POC_Time", "Total_Pssc_Time", "Total_End_To_End_time", "Total_AS_Approval_Time", "Questionnaire_Waiting_time", "Original_End_SLA", "Service", "Offering", "Request_Type_Consolidated", (
Case "Create_Date"
When "Request_Type_Consolidated" = 'Estimate'
AND "ESTM_PRVD_DTTM" IS NULL
AND "CNCLD_DTTM" IS NULL
THEN "Original_End_SLA"
ELSE "Active_date"
END) AS "Completed_Date" FROM "Tool5_3fa9"" Error: ORA-00905: missing keyword

[MarqueeCrew]

CASE "field"

 WHEN ... THEN ...

END CASE

i didn't put CASE at the end..

Try that.

[MarqueeCrew · Accepted solution]

CASE  
WHEN "Request Type Consolidated" = 'Estimate' AND
            "Estimate Provided Date" IS NULL AND
            "Cancelled Date" IS NULL
            THEN "Original End SLA"
WHEN "Request Type Consolidated" = 'Estimate' AND
            "Estimate Provided Date" IS NULL AND
            "Cancelled Date" IS NOT NULL
            THEN "Cancelled Date"
WHEN "Request Type Consolidated" = 'Estimate' AND
            "Estimate Provided Date" IS NOT NULL
            THEN "Estimate Provided Date"
WHEN "Active Date" IS NULL AND
            "Cancelled Date" IS NULL
            THEN "Original End SLA"
WHEN "Active Date" IS NULL AND
            "Cancelled Date" IS NOT NULL
            THEN "Cancelled Date"
ELSE   "Active Date"
END CASE

[msbs48]

Mark....

THANK YOU VERY MUCH FOR YOUR GENEROUS OFFER FOR THE WEBEX...   i just saw you email this morning... i was so concentrating on getting my Alteryx to work... i ignored all the messages on my corporate email...

i FINALLY did get it to work...the last part was stupidity on my part.  This is my 1st Alteryx development effort and when you are working with something 100% new, little internal help and a deadline panic mode sets in...

SO THANKS !!!

ben 

[MarqueeCrew]

When the going gets tough, the tough ask for help. That's what we're here for.

Glad that you found your way.

Cheers,
Mark (et al)