How can i write this sql statement in domo?

vikrao
vikrao Member
edited March 2022 in Magic ETL 
where a.Customer NOT IN(select distinct Customer from CustomerSalesData where InvoiceDate < '2020-07-01')

Statement below is a column I use for an sql query.. 

ROW_NUMBER() over(partition by a.Customer order by MIN(a.InvoiceDate)) as OrderNum


I am getting an error while trying to convert this statement. I am not sure how to convert it into something that Domo will understand.

Can this be done in beast mode or MagicETL?

Thanks for any help.

Tagged:

Comments

  • MarkSnodgrass
    MarkSnodgrass Coach

    For you first statement, you can split out your data and perform a number of functions with tiles and then join it back together. Something like this:

    image.png

    Your filter tile would filter to the invoice date that you want. The Group By tile would give you distinct list by just choosing customer as your column and then do some sort of aggregation, such as count. Then join it back to your original dataset and choose left join with your original dataset being on the left. After the join, use another filter tile to filter where a column from the right table is null. This would give you your customers not in your subquery.

    Your second one, you can use the Rank and Window tile to do the row number. I would suggest looking at this kb article if you haven't looked at it yet.

    https://domohelp.domo.com/hc/en-us/articles/360044876094-New-Magic-ETL-Tiles-Aggregate#3.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • vikrao
    vikrao Member

    Thanks Mark.

    I was able to use the Rank and File tile to get some of the data that I needed. I will try splitting and joining the data in MagicETL.

  • vikrao
    vikrao Member

    What if my invoice date keeps changing? I collect data for this by month so I keep changing the invoice date that I look back at.

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @vikrao what's the business question you're trying to answer in plain english?


    i suspect it's something like "which users havent paid an invoice in the last x months.

    if so rework your filter as a formula based off CURRENT_DATE

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • vikrao
    vikrao Member

    sorry for late reply. I am trying to find 'first orders from customers who have requested samples from us in the past'

  • vikrao
    vikrao Member
    edited April 2022

    To explain more precisely,

    If I have 100 sample requests in January, I want to find out how many of those customers who requested samples converted to first orders in the next 15 days, 30 days, 45 days.

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    uh...

    would it be fair to say you want to rank orders by date ascending, and just want to know the date_diff of the first order and today?


    do your rank() function in ETL and then do your datediff in Beast Mode.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • vikrao
    vikrao Member

    Yes, that would be fair to say. I will try it out and update this thread if I can get it to work.

Categories