Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

In-database Coalesce formula for Postgres using GreenPlum

Chuy
6 - Meteoroid

Hi,

 

I'm having an issue running a simple formula for an in-database calculation. I want to be able to override the value of Col_A if it happens that Col_B is not null. I'm using the "Formula In-DB" tool. I select "Col_A" as the Output Field and my SQL Expression is:

 

 coalesce("Col_B", "Col_A")

However it doesn't work. This runs fine in DataGrip but Alteryx doesn't like it. I also tried to format it as a CASE statement and still have the same issue.

 

Any idea what am I doing wrong?

 

4 REPLIES 4
fmvizcaino
17 - Castor
17 - Castor

Hello Chuy,

 

What type of error are you experiencing?

 

I've tested here in a SQL Server and it is working as expected.

 

Image 29-3.PNG

Chuy
6 - Meteoroid

I would expect that exact behavior, but at this point is giving me an error in the formula. My database is PostgreSQL and I'm accessing it through the Greenplum connector.

 

 

Chuy
6 - Meteoroid

The error text resolves to this long string:

 

Error: Formula In-DB (26): Error opening "SELECT "appl_id", "s_application_origin", "appl_dt", "R_appl_id", (coalesce("s_application_origin","channel")) AS "channel", "device", "state_cd", "dma", "zip_cd", "funnel_step", "CountDistinct_visit_id" FROM (SELECT "Left"."appl_id", "Left"."s_application_origin", "Right"."appl_dt", "Right"."appl_id" AS "R_appl_id", "Right"."channel", "Right"."device", "Right"."state_cd", "Right"."dma", "Right"."zip_cd", "Right"."funnel_step", "Right"."CountDistinct_visit_id" FROM (SELECT "appl_id", MAX("s_application_origin") AS "s_application_origin" FROM (SELECT * FROM (SELECT "n_id_application" AS "appl_id", "s_activate_account", "s_reservation_code", "s_channel_code", "s_channel_medium_code", "d_creation_application" AS "appl_dt", "s_description_product", "s_application_origin" FROM (SELECT * FROM "ci_owner"."master_oao") AS "a") AS "a" WHERE "s_application_origin" in ('MOBILE','ONLINE')) AS "a" GROUP BY "appl_id") AS "Left" RIGHT JOIN (SELECT "appl_dt", "appl_id", "channel", "device", "state_cd", "dma", "zip_cd", "funnel_step", COUNT(DISTINCT "visit_id") AS "CountDistinct_visit_id" FROM (SELECT * FROM (SELECT "os" AS "device", "evar54" AS "appl_id", "geo_dma" AS "dma", "geo_region" AS "state_cd", "geo_zip" AS "zip_cd", "date" AS "appl_dt", "visit_id", "channel", "visit_num", "funnel_step" FROM (SELECT * FROM (SELECT "id", "current_flag", "os", "visit_keywords", "evar54", "browser_useragent", "platform_useragent", "browser_height", "browser_width", "ref_domain", (case when os = 'DESKTOP' then 'DESKTOP' when os = 'MOBILE' then 'MOBILE' when device = 'x11' then 'DESKTOP' else 'OTHER' end ) AS "device", "visit_start_page_url", "visit_referrer", "application_visit", "application_start", "application_fundingscreen", "application_esignature", "application_complete", "application_declined", "application_approved", "application_review", "post_application_complete", "post_application_declined", "post_application_approved", "post_application_visit", "landing_page_url", "previous_url", "aggregated_application_field", "post_landing_page_url", "post_previous_page_url", "post_aggregated_application_field", "event_listtext", "geo_city", "geo_country", "geo_dma", (upper(geo_region)) AS "geo_region", "geo_zip", "user_agenttext", "date", "day", "hour", "minute", "millisecond", "time_of_day", "visit_id", "visits_metric_exclusions", (upper( case when visit_referrer like '%?cid=sem%' then 'paid search' when visit_referrer like '%?cid=aff' then 'affiliate' when visit_referrer like '%google%' and channel NOT IN ('sem','dis','eml','dir') then 'organic' when visit_referrer like '%yahoo%' and channel NOT IN ('sem','dis','eml','dir') then 'organic' when visit_referrer like '%/mail%' then 'email' when visit_referrer like '%nerdwallet.com%' and channel NOT IN ('sem','dis','eml','aff') then 'organic' when visit_referrer like '%duckduckgo.com%' then 'organic' when visit_referrer like '%myway.com%' then 'organic' when visit_referrer like '%facebook%' then 'social' when visit_referrer like '%yelp%' then 'organic' when visit_referrer like '%reddit%' then 'organic' when visit_referrer like '%outlook%' and channel NOT IN ('sem','dis','eml','aff') then 'email' when visit_referrer like '%xfinity.com%' then 'email' when visit_referrer like '%websnips.net%' then 'display' when visit_referrer like '%/t.co/%' then 'social' when visit_referrer like '%dealbiscuit.com%' then 'sponsorship' when visit_referrer like '%channelServiceCode=39&%' then 'sponsorship' when visit_referrer like '%magnifymoney.com%' then 'organic' when visit_referrer like '%touch.kaspersky.com%' then 'organic' when visit_referrer like '%usa.visa.com%' then 'organic' when visit_referrer like '%search.aol.com%' then 'organic' when visit_referrer like '%portal.adp.com%' then 'internal employee' when visit_referrer like '%yellowpages.com%'then 'organic' when visit_referrer like '%hustlermoneyblog.com%' then 'organic' when visit_referrer like '%search.frontier.com%' then 'display' when visit_referrer like '%?cid=oth:non:oth%' then 'mobile app' when visit_referrer like '%nortonsafe.search.ask.com%' then 'organic' when visit_referrer like '%www.bbvacompass.com' then 'direct' when visit_referrer like '%www.bbvacompass.com%' and channel NOT IN ('sem','dis','eml','aff') then 'direct' when visit_referrer like '%dnserrorassist.att.net%' then 'organic' when visit_referrer like '%/sessionExpired?%' then 'sso expired' when visit_referrer like '%/resume?%' then 'resume' when visit_referrer like '%?source=affiliate%' then 'affiliate' when visit_referrer like '%online-banking%' then 'online banking' when visit_referrer like '%online.bbvacompass.com/apply/error%' then 'error' when visit_referrer like '%secure.bbvacompass.com/locations/error.jsp%' then 'error' when visit_referrer like '%?cid=eml:%' then 'email' when visit_referrer like '%www.valuepenguin.com%' then 'organic' when visit_referrer like '%cdn.plaid.com%' then 'other referral site' when visit_referrer like '%crep.myappzcollection.com/%' then 'other referral site' when visit_referrer like '%clearspend.bbvacompass.com%' then 'clearspend site' when visit_referrer like '%USA/TX/Carrollton/3640-N-Josey-Ln/%' then 'direct' when visit_referrer like '%https://webmail.uwmail.com%' then 'email' when visit_referrer like '%echeck.com%' then 'organic' when visit_referrer like '%search.handy-tab.com%' and channel NOT IN ('sem','dis','eml','aff') then 'organic' when visit_referrer like '%gopher.com%' then 'organic' when visit_referrer like '%search.tb.ask.com%' then 'organic' when visit_referrer like '%?app=%' then 'mobile app' when visit_referrer like '%www.clipsit.net%' then 'display' when visit_referrer like '%cid%3Ddis:ret:gdn%' then 'display' when visit_referrer like '%New_Hire_Direct_Deposit_Authorization%' then 'internal employee' when visit_referrer like '%icims%' then 'internal employee' when visit_referrer like '%www.smarter.com%'and channel NOT IN ('sem','dis','eml','aff') then 'organic' when visit_referrer like '%search.fulltabsearch.com%' and channel NOT IN ('sem','dis','eml','aff') then 'organic' when visit_referrer like '%email10.godaddy.com%' then 'email' when channel = 'aff' then 'affiliate' when channel = 'seo' then 'organic' when channel = 'sem' then 'paid search' when channel = 'spo' then 'sponsorship' when channel = 'soc' then 'social' when channel = 'eml' then 'email' when channel = 'dir' then 'direct' when channel = 'mob' then 'mobile app' when channel = 'olb' then 'online banking' when channel = 'dis' then 'display' when va_closer_detail like '%app:::' then 'mobile app' when va_closer_detail like 'oth:non:oth%' then 'mobile app' when va_closer_detail like 'oth:oth:oth%' then 'mobile app' else 'unknown' end ) ) AS "channel", "post_campaign_custom1", "platform", "post_campaign_custom2", "post_campaign_custom3", "post_campaign_custom4", "post_campaign_custom5", "post_campaign_custom6", "post_campaign_custom7", "post_campaign_custom8", "ad_size", "ad_location", "post_campaign_custom9", "parent_product", (CASE WHEN PRODUCT = 'CHKG' THEN 'CHECKING' WHEN SUB_PRODUCT <> 'MMA' AND SUB_PRODUCT <> 'CD' AND PRODUCT = 'SAV' THEN 'SAVINGS' WHEN PRODUCT = 'credit cards' THEN 'CREDIT CARD' WHEN SUB_PRODUCT = 'MMA' THEN 'MONEY MARKET' WHEN SUB_PRODUCT = 'CD' THEN 'CERTIFICATE OF DEPOSIT' WHEN PRODUCT = 'LEND' THEN 'EPL' WHEN PRODUCT = 'prepaid cards' THEN 'PREPAID CARDS' WHEN sub_product = 'FC' THEN 'CHECKING' WHEN SUB_PRODUCT = 'CCNC' THEN 'CHECKING' WHEN SUB_PRODUCT = 'CCS' THEN 'SAVINGS' WHEN SUB_PRODUCT = 'easy checking' THEN 'CHECKING' ELSE NULL END ) AS "product", "sub_product", "rundt", "dayid", "va_closer_detail", "va_closer_id", "va_finder_detail", "va_finder_id", "va_instance_event", "va_new_engagement", "visit_num", (CASE WHEN post_aggregated_application_field LIKE '%:app started:%' THEN 'APPLICATION START' WHEN post_aggregated_application_field LIKE '%:end' and sub_product <> 'clearspend' THEN 'APPLICATION COMPLETE' WHEN post_aggregated_application_field LIKE '%:app complete:' and sub_product <> 'clearspend' THEN 'APPLICATION COMPLETE' WHEN post_aggregated_application_field LIKE '%:end' and sub_product <> 'clearspend' THEN 'APPLICATION COMPLETE' WHEN post_aggregated_application_field = 'clearspend:2 add secondary cardholder' AND sub_product = 'clearspend' then 'APPLICATION COMPLETE' ELSE null END) AS "funnel_step" FROM (SELECT * FROM (SELECT * FROM "ci_owner"."master_clickstream") AS "a" WHERE "date"::date BETWEEN to_date('20180101','YYYYMMDD') AND current_date) AS "a") AS "a" WHERE "funnel_step" IS NOT NULL) AS "a") AS "a" WHERE "funnel_step" = 'APPLICATION START') AS "a" GROUP BY "appl_dt", "appl_id", "channel", "device", "state_cd", "dma", "zip_cd", "funnel_step") AS "Right" ON "Left"."appl_id" = "Right"."appl_id") AS "a"": No Columns Returned.

 

This is the workflow I have so farThis is the workflow I have so farand this is the definition of the formulaand this is the definition of the formula

simonaubert_bd
13 - Pulsar

Hello @Chuy 
1/ Have you tried to create a temporary table (or several). Your query is really big
2/ Since a few years, Alteryx now distingue psotgresql and greenplum

Best regards,

Simon

Labels