CRM Sales Formula Help

mattwelykholowa
mattwelykholowa Member
in Magic ETL

Hi, I need help in creating a formula to write referral data to a new column. In the Dataset we have Columns A:C and I need to create a new Column D (Referred By) based on this logic:

Where there are common engagement ID's (In this case 1234 or 5678) if 'inherited Sale' = True then set Referred By to Full Name.


image.png

Thank you in Advance!

Comments

  • MarkSnodgrass
    MarkSnodgrass Coach

    I would suggest doing this in Magic ETL since you need to access previous rows. You can do this by filtering to Inherited Sale = True and then join it back to your original dataset and choose left join with your original dataset being on the left side. Join on Engagement ID and and Full Name. Records that show from the filtered tile, will be your Referred By column. The ETL would look something like this:

    image.png


    **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.
  • mattwelykholowa
    mattwelykholowa Member

    Mark I need to buy you a drink!!! :) Thank you! Worked like a charm!

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @MarkSnodgrass careful!

    you are making the assumption that FILTER ROWS only contains one row per Engagement ID where filter is true. if you are incorrect you would be causing row growth in the JOIN.

    if you want the FIRST or LAST instance of filter is true, then you should add a RANK & WINDOW tile after the filter. calculate the row number Partition By Engagement_ID order by activity_date ascending or descending as appropriate then filter where row_number = 1.

    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"

Categories