1

I am trying to bring content from a non sendable data extension where I am matching the first 2 locations from my sendable data extension. Example of data in the sendable data extension: Holland, Belgium,Luxembourg

based on this I am referencing the first two locations in a non sendable data extension where I have location data in two fields: a country & a city field.

I want to be able to pull the content from the non sendable DE for when is a match on my primary location (that is successful), if I don't have a match for my primary location then check the secondary location. If there is a match bring content associated with the secondary location; if no match is found pull a default content area.

The problem I have at the moment is that nothing is shown for the default content piece. I had an issue when the secondary location wasn't picked up for content but I realised that was because of space separator that was coming from BuildRowsetFromString so I had to use Trim and now I can show content for secondary location but nothing is displayed for default content.

Any guidance is much appreciated.

   %%[

SET @Location=AttributeValue('Product: Description')
SET @virgula=','

if IndexOf(@Location,@virgula) > 0 then 

     SET @rs = BuildRowsetFromString(@Location,',')
     SET @PrimaryLocation = Field(Row(@rs,1),1)
     SET @SecondaryLocation = Field(Row(@rs,2),1)
else 
SET @PrimaryLocation=@Location
SET @SecondaryLocation=@Location

endif 

]%%


%%[SET @rows = LookupRows("Blog Post Reference Table","Default","1")
set @rowCount = rowcount(@rows)
if @rowCount > 0 then

  set @match = 0
  set @match2= 0

  for @i = 1 to @rowCount do

    if @match == 0 then
      set @row = row(@rows, @i) /* get row based on counter */
      set @Article = field(@row,"Article Title")
      set @Description = field(@row,"Description")
      set @Image = field(@row,"Image")
      SET @LinkTrip=field(@row,"Permalink")
      set @UrlFinal=RedirectTo(@LinkTrip)
      set @defaultURl='https://www.trafalgar.com'
      set @LocationCity=field(@row,"Location_City")
      set @bara='|'
      set @LocationCountry=field(@row,"Location_Country")


      if IndexOf(@LocationCity,@bara) > 0 then 
        SET @LocationCity2=Substring(@LocationCity,1, Subtract(IndexOf(@LocationCity,@bara),1))
      else 
        SET @LocationCity2=@LocationCity
      endif

       if IndexOf(@LocationCountry,@bara) > 0 then 
        SET @LocationCountry2=Substring(@LocationCountry,1, Subtract(IndexOf(@LocationCountry,@bara),1))
      else 
        SET @LocationCountry2=@LocationCountry
      endif

      if (@PrimaryLocation == @LocationCity2) or (@PrimaryLocation == @LocationCountry2) then
        SET @match = 1
      endif

SET @SecondaryNOspace=trim(@SecondaryLocation)
endif


]%%



%%[if @match==1 and @i == @rowCount then]%%

From the blog piece for primary location: <br>
Article name:%%=v(@Article)=%%<br>

Primary location: %%=v(@PrimaryLocation)=%%<br><br>
Description: %%=v(@Description)=%%
<br>
<a href="%%=RedirectTo(IIF(@nolink,v(@defaultURl),v(@UrlFinal)))=%%"><img src="%%=v(@Image)=%%" width="600" height="400"></a>


%%[elseif  (@SecondaryNOspace == @LocationCity2) or (@SecondaryNOspace == @LocationCountry2) then
        SET @match2 = 1

 if @match2==1 and @i == @rowCount then]%%

From the blog piece for secondary location: <br>
Article name:%%=v(@Article)=%%<br>

Secondary location: %%=v(@SecondaryNOspace)=%%<br>

Description: %%=v(@Description)=%%
<br>
<a href="%%=RedirectTo(IIF(@nolink,v(@defaultURl),v(@UrlFinal)))=%%"><img src="%%=v(@Image)=%%" width="600" height="400"></a>



%%[ else if @i == @rowCount then]%%
DEFAULT CONTENT because there was no match for primary or secondary locations 

%%[
    endif
    endif
    endif
  next @i 
endif
]%%

Giulietta

enter image description here enter image description here

1
  • would be great if you can share the columns of target data extension and look up non sendable data extension. It is very hard for me to visualise without the columns.
    – Naveen VM
    Commented May 13, 2020 at 10:56

1 Answer 1

1

From my understanding you are actually doing too much inside the for loop.

Essentially, what you want is just a FOR loop which contains a bit of data cleanup and one if / elseif / else condition.

I cleaned the code up a bit to reflect that. The @match variables are completely removed. I think the main issue was that inside your loop you were checking for the unnecessary "match" variables, and test whether @i equals the @rowCount.

@i is the current run of the loop. @rowCount is the last run.

Effect: the code inside the loop is only executed once, namely for the last run. Which makes no sense in your usecase, I believe. You want to check matches for each found location in your non-sendable DE, not just the last.

Also:

  • Moved some of the variables out of the for loop, no need to define them in each loop iteration.
  • Added indentation to make code more readable and structurally clearer.
  • Changed the "IIF logic" in the links to a more basic IF EMPTY logic.

I have not tested this code, but it should provide a good starting point.

%%[

/* global variables */

SET @defaultURL ='https://www.trafalgar.com'
SET @bara= '|'
SET @virgula=','
SET @location = AttributeValue('Product: Description')


IF IndexOf(@Location,@virgula) > 0 THEN 
  SET @rs = BuildRowsetFromString(@Location,@virgula)
  SET @primaryLocation = Field(Row(@rs,1),1)
  SET @secondaryLocation = Field(Row(@rs,2),1)
ELSE
  SET @primaryLocation = @location
  SET @secondaryLocation = @location  
ENDIF 
/*
]%%

Primary location: %%=v(@primaryLocation)=%%
<br>
Secondary location: %%=v(@secondaryLocation)=%%
<br>
%%[
*/

SET @rows = LookupRows("Blog Post Reference Table","Default","1")
SET @rowCount = rowcount(@rows)

IF @rowCount > 0 then
/* entry found in nonsendable DE */

  FOR @i = 1 to @rowCount do
  /* get all references */
    SET @row = row(@rows, @i) /* get row based on counter */
    SET @Article = field(@row,"Article Title")
    SET @Description = field(@row,"Description")
    SET @Image = field(@row,"Image")
    SET @linkTrip = field(@row,"Permalink")
    SET @locationCity = field(@row,"Location_City")
    SET @locationCountry = field(@row,"Location_Country")

    /* fallback */
    IF empty(@linkTrip) THEN
      SET @linkTrip = @defaultURL
    ENDIF

    /* cleanup data */
    if IndexOf(@locationCity,@bara) > 0 THEN 
      SET @locationCity = Substring(@locationCity,1, Subtract(IndexOf(@locationCity,@bara),1))
    endif

    if IndexOf(@locationCountry,@bara) > 0 THEN 
      SET @locationCountry = Substring(@locationCountry,1, Subtract(IndexOf(@locationCountry,@bara),1))
    endif
    /* end cleanup */

    IF (@primaryLocation == @locationCity) 
    or (@primaryLocation == @locationCountry) THEN
    ]%%
      From the blog piece for primary location:<br>
      Article name:%%=v(@Article)=%%<br>
      <br>
      Primary location: %%=v(@primaryLocation)=%%<br>
      <br>
      Description: %%=v(@Description)=%%
      <br>
      <a href="%%=RedirectTo(@linkTrip)=%%"><img src="%%=v(@Image)=%%" width="600" height="400"></a>
      %%[
    ELSEIF (@secondaryLocation == @locationCity) 
    or (@secondaryLocation == @locationCountry) THEN
      ]%%
        From the blog piece for secondary location:<br>
        Article name: %%=v(@Article)=%%<br>
        <br>
        Description: %%=v(@Description)=%%
        <br>
        <a href="%%=RedirectTo(@linkTrip)=%%"><img src="%%=v(@Image)=%%" width="600" height="400"></a>
      %%[ 
    ELSE 
      ]%%
        DEFAULT CONTENT because there was no match for primary and secondary location
      %%[
    ENDIF
  NEXT @i 
ELSE
]%%
  DISPLAY SOME ERROR MESSAGE, no data found.
%%[  
ENDIF
]%%

Hope this helps!

You must log in to answer this question.

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