Help Needed: Moving from SQL to Alteryx for Dynamic Inventory Overview

[Maggie_yo]

Hey Alteryx Community,

I'm diving into the world of Alteryx, and I could really use some advice from the experts!

I'm coming from an SQL background and I've cracked a case using SQL, but now I'm all ears to learn how to do the same thing in Alteryx.

To provide some context, I have successfully solved a specific case using SQL, but now I'm eager to replicate the same logic within Alteryx.

The goal of the case is to create an inventory overview that dynamically reflects the availability of products based on demand deadlines.

This is what I've been cooking up in SQL:

with base as (
SELECT 
    material_number,
    demand_deadline,
    MIN(demand_deadline) OVER (PARTITION BY material_number) AS min_start_date,
    MAX(demand_deadline) OVER (PARTITION BY material_number) AS max_end_date,
    LEAD(demand_deadline) OVER (PARTITION BY material_number ORDER BY demand_deadline) AS next_demand_deadline,
    SUM(demand_qqt) OVER (PARTITION BY material_number ORDER BY demand_deadline ASC) AS acc_demand_qqt
FROM `stg_demand` 
), 

daterange AS(
SELECT DISTINCT 
    b.material_number,
    day
FROM base AS B
    CROSS JOIN UNNEST(GENERATE_DATE_ARRAY( B.min_start_date, B.max_end_date , INTERVAL 1 DAY)) AS day
)

SELECT
    D.day,
    d.material_number,
SUM(acc_demand_qqt) as acc_demand
FROM daterange AS D
INNER JOIN base AS B 
    ON D.day >= B.demand_deadline 
    AND (D.day < B.next_demand_deadline OR next_demand_deadline IS NULL)
    AND D.material_number = B.material_number

group by 1,2
order by day

I'm hoping you can show me the ropes. How do I take this SQL logic and turn it into an Alteryx workflow?

I'm especially curious about things like generating date ranges and mimicking window functions like LEAD.

If you could share any tips, tricks, or even a step-by-step example, I'd be over the moon! ✨

Thanks a million in advance for your help! Looking forward to soaking up your wisdom! 💡

Cheers

[rzdodson]

@Maggie_yo unsure if you have already seen the various SQL options in the Input tool. But, here is an example.

Here is some additional literature on establishing OLEDB and ODBC connections: https://help.alteryx.com/10.0/DatabaseConnections.htm

Once you establish the connection to your data warehouse environment, you will see all of the fields that are available. You can click on each field, and Alteryx will start building out the SQL query for you. I have put the Alteryx Visual Query Builder and SQL Editor side-by-side so you can see how Alteryx is helping build out the SQL query.

Finally, here is a great post walking through the various types of joins: https://help.alteryx.com/20231/designer/join-tool#:~:text=Alteryx%20Designer%20automatically%20selec....

Starting from the "View the Output"  in the last link, you'll see a detailed explanation with visuals around recreating SQL joins in using Alteryx tools should you not already have your SQL query built out.

If this helps, please mark as "Solved" to help out future users. :)

[Maggie_yo]

Thank you @rzdodson for your reply!

Just a heads up, the SQL I shared is from BigQuery, so I'm not totally sure if I can connect it to Alteryx, as it the tool used to connect BigQuery is deprecated . 

By the way, I'm already getting the hang of the Joins tool, so thanks for pointing me in that direction!

I could use a hand in figuring out how to build this date range logic (I've been tinkering with the "Multi-row Formula" tool), and I'm also trying to wrap my head around window functions and that CROSS JOIN UNNEST.

Any insights you've got would be awesome!