0

I have two data extensions:

  • Data extension 1: API_Onboarding_OnlineMemberships has the following column: SUBS_START_DATE (this DE gets the emails)
  • Data extension 2: Customers has the following column: DATE_OF_FIRST_SUB_AEST

Here's the amp script I've updated in the email:

%%[
SET @SUBS_START_DATE = Lookup("API_Onboarding_OnlineMemberships","SUBS_START_DATE","CUSTOMER_INTERNAL_NS_ID", @CUSTOMER_INTERNAL_NS_ID)
SET @DATE_OF_FIRST_SUB_AEST = Lookup("Customers","DATE_OF_FIRST_SUB_AEST","CUSTOMER_INTERNAL_NS_ID", @CUSTOMER_INTERNAL_NS_ID)

IF @SUBS_START_DATE > @DATE_OF_FIRST_SUB_AEST THEN
  SET @output = "Welcome Back"
ELSE
  SET @output = "Welcome"
ENDIF
]%%
%%=v(@output)=%%

It should show 'Welcome Back' if @Subs_Start_Date is today and the @Date_of_first_sub_aest is yesterday...

@CUSTOMER_INTERNAL_NS_ID is the Subscriber key for both data extensions, but not necessarily the primary key for both.

This ampscript doesn't render the right data i.e., Welcome or Welcome back email. Am I missing something? enter image description here

Improve this question
1

1 Answer 1

Reset to default
1

It is definitely possible and is something that happens fairly often. See below for example of how to do this:

%%[
SET @subsDate = Lookup("Test1","Subs_Date","MyPrimaryKeyName",@myPrimaryKeyValue)
SET @dateOfFirstSub = Lookup("Test2","Date_of_first_Sub","MyPrimaryKeyName",@myPrimaryKeyValue)

IF @subsDate > @dateOfFirstSub THEN
  SET @output = "Welcome"
ELSE
  SET @output = "Welcome Back"
ENDIF
]%%
%%=v(@output)=%%

This gathers the values from each DE based off your primarykey (which is being used as a foreign key to match based on the shared relationship) to determine the row that houses this information. It then compares these variables to determine the output (which I pushed to a variable @output for the example, but you can also use Output() function or close the AMPscript block and write as HTML, etc. and then open the next block for the ELSE statement.

I then output the output variable via an inline call of the v() function that then says either 'Welcome' or 'Welcome Back' depending on the values in the 2 lookup vars.

Improve this answer
10
  • Thanks @gortonington I've edited my question with the updated query, following your recommendations. But it still doesn't show the right data. Do you have any recommendations for this?
    – Preksha Rathore
    Apr 29, 2022 at 2:32
  • Can you share sample outputs of the two lookups? Also are these being stored as strings or dates in the DEs? Is there a timezone difference? If you provide more info @PrekshaRathore, I may be able to better assist as I do not believe it is the code at this point, but more likely something with the data
    – Gortonington
    Apr 29, 2022 at 13:39
  • Thank you Gortonington. I've added a screenshot of how the fields look like in the data extension. As seen, the setting for Subs_start_date is 'Date'. Also, in the data extension, the date comes something like this: 'Sunday, 20 March 2022 12:00 AM' - potentially that's an issue.
    – Preksha Rathore
    May 1, 2022 at 23:40
  • Thanks @PrekshaRathore - but is DATE_OF_FIRST_SUB_AEST also a date field or a string? If it is a string, then that is likely the issue you are facing. and if it is a string can you give an example of output (and format - e.g. DD.MM.YYYY). Other thing to note is if the foreign key of CUSTOMER_INTERNAL_NS_ID has more than 1 entry in either DE, it will use whichever it finds first as the value to return in the Lookup - which could create discrepancy on outputs.
    – Gortonington
    May 2, 2022 at 13:26
  • 1
    @PrekshaRathore - I get the feeling it is taking the dates and for some reason turning them into Strings. Try wrapping them in StringToDate() - StringToDate(@subsDate) > StringToDate(@dateOfFirstSub) and see if that helps to force a date comparison...Seems odd though why that would be happening. Could also try DateParse() AMPscript function as well to force datetime data type.
    – Gortonington
    May 3, 2022 at 13:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .