CRM Sales Formula Help

mattwelykholowa

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.

Thank you in Advance!

MarkSnodgrass

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:

mattwelykholowa

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

jaeW_at_Onyx

@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.