Recreate Multi-Row tool in-db??

[jhertel]

I am working in-db in a Snowflake environment and find I'm unable to recreate the formula in yellow in the attached excel sheet/ in the Desktop workflow.

Essentially, I need a formula in-db that refers to itself, in the way that I would use the Designer Multi-Row tool. F.ex. if I defined [New Field] in the Multi-Row tool, I could use [Row-1:New Field] in the expression. 

However, in in-db, I can only use Lead/Lag functions in a new field that refer to already existing fields. Is there any way out there to do this in-db? (Streaming in and out is not an option. There is too much data).

I've attached sample data, the way the workflow would look with regular Designer tools, and also a stub of an in-db workflow (not necessary i think, but just to be thorough).

Any help would be appreciated!! Thank you!

[apathetichell · Accepted solution]

Hey -> this was pretty fun - especially given the restrictions of Alteryx/In-Db.

1) No - WITH (CTAS).

2) No UDF - just because I wasn't sure how it would work.

What I ended up doing was 

1) Creating a running total of the days_since field for each consumerID

2) Creating a term called Skew which looked at the difference between the Fill_Date and the normalized date (cumulative Days Since + first date)

3) Capturing the max skew for each consumerID (this required an extensive subquery because it's a partitioned max of a windowed term)

4) Comparing the Fill_Date, the skew, the days since and the max_skew to arrive at a snowflake created date.

Note -> I included your initial data purely for testing purposes -> the excel exhaust date is only for testing vs the Snowflake computed date.

Note 2-> you may want to put the subqueries in standalone Connect-In-DB tools and then use join -> vs the all in one approach here.

select
CONSUMERID, FILL_DATE, BRAND_NAME, DAYS_SUPPLY, FILL_RANK,DAYS_TO_NEXTFILL,
CALCULATED_EXHAUST_DATE,
SKEW_DELTA,
SKEW,
MAX_SKEW,
SUM (SKEW_DELTA) OVER (PARTITION BY CONSUMERID ORDER BY FILL_DATE) AS SKEW_FACTOR,

FIRST_FILL_DATE+DAYS_SINCE+LEAST(IFNULL(SKEW_FACTOR,0),MAX_SKEW) AS SNOWFLAKE_CALC_DATE,
SNOWFLAKE_CALC_DATE=CALCULATED_EXHAUST_DATE AS TEST

FROM (
SELECT T.*, MAX_SKEW,

GREATEST(T.skew - LAG(T.skew, 1) OVER (PARTITION BY T.CONSUMERID ORDER BY FILL_DATE),0) AS SKEW_DELTA
FROM
(
SELECT *,
DAYS_SINCE+FIRST_FILL_DATE AS SECONDARY_CALC,
IFNULL(
greatest(FILL_DATE-LAG(SECONDARY_CALC, 1) OVER (PARTITION BY CONSUMERID ORDER BY FILL_DATE),0)
,0) AS skew
FROM (
SELECT *,
SUM (DAYS_SUPPLY) OVER (PARTITION BY CONSUMERID ORDER BY FILL_DATE) AS DAYS_SINCE
from "MULTI_ROW_FORMULA"."PUBLIC"."FORMULA_EXAMPLE" s
JOIN
(select CONSUMERID AS MATCH_CONSUMER, FILL_DATE AS FIRST_FILL_DATE,DAYS_SUPPLY AS FIRST_DAYS FROM
"MULTI_ROW_FORMULA"."PUBLIC"."FORMULA_EXAMPLE"
WHERE FILL_RANK=1
GROUP BY CONSUMERID, FILL_DATE, DAYS_SUPPLY
) f
ON
s.CONSUMERID= f.MATCH_CONSUMER,
ORDER BY CONSUMERID, FILL_DATE)) t
JOIN

(SELECT
CONSUMERID,
MAX(SKEW) AS MAX_SKEW
FROM(
SELECT CONSUMERID,
DAYS_SINCE+FIRST_FILL_DATE AS SECONDARY_CALC,
IFNULL(
(FILL_DATE-LAG(SECONDARY_CALC, 1) OVER (PARTITION BY CONSUMERID ORDER BY FILL_DATE))
,0) AS skew
FROM(
SELECT *,
SUM (DAYS_SUPPLY) OVER (PARTITION BY CONSUMERID ORDER BY FILL_DATE) AS DAYS_SINCE
from "MULTI_ROW_FORMULA"."PUBLIC"."FORMULA_EXAMPLE" s
JOIN
(select CONSUMERID AS MATCH_CONSUMER, FILL_DATE AS FIRST_FILL_DATE,DAYS_SUPPLY AS FIRST_DAYS FROM
"MULTI_ROW_FORMULA"."PUBLIC"."FORMULA_EXAMPLE"
WHERE FILL_RANK=1
GROUP BY CONSUMERID, FILL_DATE, DAYS_SUPPLY
) f
ON
s.CONSUMERID= f.MATCH_CONSUMER,
ORDER BY CONSUMERID, FILL_DATE))
GROUP BY CONSUMERID) SKEW

ON T.CONSUMERID=SKEW.CONSUMERID
) ORDER BY CONSUMERID,FILL_DATE

[jhertel]

Fantastic - I did set it up in several formula tools for easier QC, but it absolutely worked a treat! Thank you!