2

I'm pretty new to ampscript and we are designing a template which will order the content based on the customer next purchase propensity. For example we have 5 genre values for a customer which will define how the content will appear in an email.

Concerts=0.741, ArtsTheatreComedy=0.129, Family=0.05, Sports=0.06, Festival=0.021

I'm Ok with DE lookups and retrieving content but I cannot figure how do to use ampscript to give me the variables in assending order based on their value so that I can retrieve content in the specific order. From the above example when this specific customer opens his email he should see the content in order as Concert, ArtsTeatre, Sports, Family and Festival.

Thanks

K

2 Answers 2

1

I posted an example of retrieving ordered rows using the LookupOrderedRows AMPScript function on my blog here (There are other examples, too):

Here's the gist:

%%[
var @rows, @row, @rowCount, @numRowsToReturn, @lookupValue, @i

set @lookupValue = "whee"
set @numRowsToReturn = 0 /* 0 means all */
set @rows = LookupOrderedRows("DataExtensionName",@numRowsToReturn,"DEColumn1 desc, DEColumn2 asc","LookupColumn", @lookupValue)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

for @i = 1 to @rowCount do

var @DEColumn1, @DEColumn2
set @row = row(@rows,@i) /*get row based on loop counter */
set @DEColumn1 = field(@row,"DEColumn1")
set @DEColumn2 = field(@row,"DEColumn2")

]%%

Row %%=v(@i)=%%, DEColumn1 is %%=v(@DEColumn1)=%%, DEColumn2 is %%=v(@DEColumn2)=%%

%%[

next @i ]%%

%%[ else ]%%

No rows found

%%[ endif ]%%

You can specify ASC or DESC in the third parameter in the LookupOrderedRows() function.

6
  • Hi Adam, Thank you for you answer. The propensity scores are columns for a customer. Each customer has 5 propensity scores in a single row. so Concerts=0.741, ArtsTheatreComedy=0.129, Family=0.05, Sports=0.06, Festival=0.021 are in one single row. I want to order the columns in descending order based on their value and then use that information to display content. How would I do this?
    – user5214
    Commented Jan 10, 2014 at 16:15
  • AFAIK, there's not a good way to do that in AMPScript. If you're using Data Extensions, I'd suggest pivoting the propensity scores into rows instead of columns. Then you could retrieve them sorted. Arrays are a weakness in AMPScript. You could possibly use build a rowset in AMPScript and use Server-side JavaScript to sort it, but it would be pretty involved. Commented Jan 10, 2014 at 16:22
  • Thanks. Yes, I think its not as straight forward as I thought.
    – user5214
    Commented Jan 10, 2014 at 17:15
  • Involved, and send speed would suffer. Is there anyway to pull the information in sorted from your side first? You could then build a rowset from the presorted string. Commented Jan 10, 2014 at 17:15
  • @KellyJAndrews set array= Concat (Concerts,"|", ArtsTheareComedy, "|",ArtsTheareComedy ,"|", Family, "|", Sports, "|", Festival) Set rows = BuildRowsetFromString(array, '|') for i = 1 to RowCount(rows) do Set row = Row(rows, i) ]%% <p>%%= Field(row,"Value") =%%</p> %%[ next @i ]%% Can I do something in the for loop to sort the values?
    – user5214
    Commented Jan 10, 2014 at 17:54
1
%%[

/* define variables */
Var  @Concerts, @ArtsTheareComedy, @Family, @Sports, @Festival, @array, @rows, @Position1, @Position2, @Position3, @Position4, @Position5, @ContentFolder

/* Set variable */
set @ContentFolder="my contents\Major Genre\"
set @Concerts= concat (Next_Purchase_Major_Genre_Concerts,"-Concerts")
set @ArtsTheatreComedy=concat(Next_Purchase_Major_Genre_Arts_Theatre_Comedy,"-ArtsTheatreComedy")
set @Family=concat(Next_Purchase_Major_Genre_Family_Attractions, "-Family")
set @Sports=concat(Next_Purchase_Major_Genre_Sports, "-Sports")
set @Festival=concat(Next_Purchase_Major_Genre_Festival,"-Festival")

]%%

<script runat=server>
//Run a Java Script to create propensity score array and sort desending

    Platform.Load("Core","1");
    var Score=[Variable.GetValue("@Concerts"),Variable.GetValue("@ArtsTheatreComedy"), Variable.GetValue("@Family"),Variable.GetValue("@Sports"),Variable.GetValue("@Festival")]    
    Score.sort(function (a, b) {
        return a.localeCompare(b);});
    //Score.reverse();

// Allocate positions to each genre based of propensity score (high to low)
    Variable.SetValue("@Position1", Score[4])
    Variable.SetValue("@Position2", Score[3])
    Variable.SetValue("@Position3", Score[2])
    Variable.SetValue("@Position4", Score[1])
    Variable.SetValue("@Position5", Score[0])

</script>

 %%[
/* set AMPcript variables from Java script variables */
set @position1 = Substring(@Position1,add(IndexOf(@position1,"-"),1),Length(@Position1))
set @position2 = Substring(@Position2,add(IndexOf(@position2,"-"),1),Length(@Position2))
set @position3 = Substring(@Position3,add(IndexOf(@position3,"-"),1),Length(@Position3))
set @position4 = Substring(@Position4,add(IndexOf(@position4,"-"),1),Length(@Position4))
set @position5 = Substring(@Position5,add(IndexOf(@position5,"-"),1),Length(@Position5))
]%% 
2
  • 1
    Yes this does work, however, a word of warning. There are considerable speed hits from our implementation of SSJS that can impact send speeds. If this is in a landing page, it's not as big of an issue. I would have recommended this solution otherwise. You might want to try to do some testing before performing this to scale. Commented Jan 22, 2014 at 15:26
  • Thanks Kelly, I'm preparing a dummy dataset to run through the testing. Will see how that goes.
    – user5214
    Commented Jan 22, 2014 at 15:41

You must log in to answer this question.

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