/SFMC-AMPscript-output-random-rows-from-a-data-extension.amp
Last active Nov 15, 2019
Output random rows from a data extension with AMPscript
%%[ | |
var @debug | |
set @debug = 1 | |
var @rn | |
var @rows | |
var @rowCount | |
var @arr | |
var @max | |
set @rows = LookupRows("DataExtensionTest", "lookupKey", 1) | |
set @rowCount = rowCount(@rows) | |
set @arr = "" | |
/* this increases the likeihood that all of the rows are represented */ | |
set @max = multiply(@rowCount,10) | |
/* generate a delimited string of random numbers, at least one for each row in the DE */ | |
for @i = 1 to @max Do | |
set @rn = concat('{', random(1,@rowCount),'}') | |
if indexof(@arr, @rn) < 1 then | |
set @arr = concat(@arr, @rn) | |
endif | |
next @i | |
if @debug == 1 then | |
output(concat("<br>arr(1): ", @arr)) | |
endif | |
/* loop through and replace each random number with a value */ | |
for @i = 1 to @rowCount do | |
set @row = row(@rows, @i) | |
set @subscriberKey = field(@row, "subscriberKey") | |
set @arr = replace(@arr, concat("{", @i ,"}"), concat("{", @subscriberKey ,"}")) | |
next @i | |
set @arr = replace(replace(replace(@arr,"}{","|"),"}",""),"{","") | |
if @debug == 1 then | |
output(concat("<br>arr(2): ", @arr)) | |
endif | |
/* build a rowset from the delimited string */ | |
set @outputRows = buildRowSetFromString(@arr, "|") | |
set @outputRowCount = rowCount(@outputrows) | |
/* iterate through the random rowset */ | |
for @i = 1 to @outputRowCount do | |
set @outputRow = row(@outputRows, @i) | |
set @subscriberkey = field(@outputRow, 1) | |
output(concat("<br>subscriberkey",@i, ": ", @subscriberkey)) | |
var @firstName | |
set @firstName = Lookup("DataExtensionTest","FirstName", "SubscriberKey", @subscriberkey) | |
output(concat(", firstName: ", @firstName)) | |
next @i | |
]%% |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment