0

I'm wondering if it's possible to use an AMPscript loop to build a concat function from Data Extension rows.

Let's say I want to create a Base64 encoded string of pipe delimited data from rows in a Data Extension. Pretend for now that there are just 3 rows in that DE, then I could use the following code:

%%[
var @rows, @row, @binaryStream

set @rows = LookupRows("My Data Extension","Campaign", "Welcome")

set @row1 = row(@rows,1)
set @row2 = row(@rows,2)
set @row3 = row(@rows,3)

set @binaryStream = Base64Encode(Concat(
  field(@row1,"id"),'|',field(@row1,"First Name"),'|',field(@row1,"Last Name"),Char(13),
  field(@row2,"id"),'|',field(@row2,"First Name"),'|',field(@row2,"Last Name"),Char(13),
  field(@row3,"id"),'|',field(@row3,"First Name"),'|',field(@row3,"Last Name")))
]%%

This would produce output, which when Base64 decoded would look like this:

1|Jack|Johns
2|Jane|Jones
3|Luke|Smith

That's fine. But what if I wanted to build the @binaryStream based on the number of rows in the DE using a FOR loop? For example, for @i = 1 to RowCount(@rows) do... Can this be done in such a context?

3

Are you looking for something like this...

%%[
var @rows, @row, @binaryStream
set @rows = LookupRows("My Data Extension","Campaign", "Welcome")
SET @binaryStream = ""

FOR @i = 1 TO ROWCOUNT(@rows) DO
    SET @row = ROW(@rows,@i)    
    SET @binaryStream = CONCAT(@binaryStream,field(@row,"id"),'|',field(@row,"First Name"),'|',field(@row,"Last Name"))

    /* Add linefeed if this is not the last row */
    IF @i != ROWCOUNT(@rows) THEN
        SET @binaryStream = CONCAT(@binaryStream,Char(13))
    ENDIF
NEXT @i
SET @binaryStream = Base64Encode(@binaryStream)
]%%
| improve this answer | |
  • Works like a charm, thanks! I tried something similar but was approaching it wrong. Interesting to see that you can set a variable on itself SET @binaryStream = Base64Encode(@binaryStream), I've never tried that before! – Eliot Harper May 19 '17 at 6:16
  • Great answer @AndrewMcLeod! I have been looking for a way to add linefeeds, never thought to add via Char()! (boy is my face red) – Gortonington May 19 '17 at 12:32
  • @Gortonington Thanks, but I can't take any credit for putting unprintables into the output. The code was lifted from Eliot's original question. – Macca May 20 '17 at 1:22
  • @Gortonington, would you be able to join the marketing-cloud chat room and participate in a related discussion of interest to you? – Eliot Harper May 20 '17 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.