This article showcases how to successfully group and display Data Extension records using AMPscript in Salesforce Marketing Cloud.

Premise

Consider a Data Extension that contains the records of people’s names and their home addresses split in 4 different fields:

StreetName	HouseNumber	ZipCode	City	Person
Rue de Banane	34	1000	Brussels	John
Concordestraat	5	7000	Bergen	Koen
Rue de Banane	34	1000	Brussels	Jane
Rue de la Concorde	86	7000	Mons	Kim

Our goal is to group the people living at the same address, for better data visualisation and readability:

34 Rue de Banane, 1000 Brussels	John
	Jane
5 Concordestraat, 7000 Bergen	Koen
86 Rue de la Concorde, 7000 Mons	Kim

Let’s discover how to achieve this using AMPscript language in Salesforce Marketing Cloud.

Solution

AMPscript is a simple and practical scripting language, but it can also be puzzling when dealing with more complex use cases.

In order to start grouping, we first need to compile the full home addresses from 4 different fields in each record.

Instead of using AddObjectArrayItem method, we are going to simplify our code with BuildRowSetFromString, that builds an iterable object from a String.

We just need to make sure that each full address ends with a separator (such as “|”) and we only add an address to the String if it doesn’t already exist.

SET @Addresses = ""
                                
FOR @i = 1 TO @DataExtensionRowCount DO

	SET @Row = Row(@DataExtensionRows, @i)

	SET @StreetName = Field(@Row, "StreetName")
	SET @HouseNumber = Field(@Row, "HouseNumber")
	SET @ZipCode = Field(@Row, "ZipCode")
	SET @City = Field(@Row, "City")

	SET @Address = CONCAT(@HouseNumber, " ", @StreetName, ", ", @ZipCode, " ", @City)

	IF INDEXOF(@Addresses, @Address) == 0 THEN
		SET @Addresses = IIF(EMPTY (@Addresses), CONCAT(@Address), CONCAT(@Addresses, "|", @Address))
	ENDIF

NEXT @i

The result of this code (variable @Addresses) will be the following:

Rue de Banane 34, 1000 Brussels|Concordestraat 5, 7000 Bergen|Rue de la Concorde 86, 7000 Mons|

We can now use BuildRowSetFromString to create iterable rows for the next step:

SET @AddressRows = BuildRowsetFromString(@Addresses, '|')

Which consists of looping through each full address and inside each loop, go over each record of the Data Extension where the combination of the 4 fields matches the full address:

SET @AddressRows = BuildRowsetFromString(@Addresses, '|')

FOR @i = 1 TO ROWCOUNT(@AddressRows) DO

	SET @AddressRow = Row(@AddressRows, @i)
	SET @AddressFromString = Field(@AddressRow, 1)

	OUTPUT(CONCAT(@AddressFromString))

	FOR @j = 1 TO @DataExtensionRowCount DO
        
		SET @Row = Row(@DataExtensionRows,@j)

		SET @StreetName = Field(@Row, "StreetName")
		SET @HouseNumber = Field(@Row, "HouseNumber")
		SET @ZipCode = Field(@Row, "ZipCode")
		SET @City = Field(@Row, "City")
		SET @Person = Field(@Row, "Person")

		SET @Address = CONCAT(@HouseNumber, " ", @StreetName, ", ", @ZipCode, " ", @City)

		IF @Address == @AddressFromString THEN

			OUTPUT(CONCAT(@Person))

		ENDIF

	NEXT @j

NEXT @i

And that’s how we can bend AMPscript to our will! Loop after loop, inside loop!

Full code

In order for the full code to work, please add the field Active to your Data Extension with a default value set to True.

This will allow AMPscript to fetch all the records at once (2500 max).

<dl>
%%[

SET @DataExtensionRows = LookupRows("MyAddresses", "Active", "True")
SET @DataExtensionRowCount = ROWCOUNT(@DataExtensionRows)

SET @Addresses = ""
                                
FOR @i = 1 TO @DataExtensionRowCount DO

	SET @Row = Row(@DataExtensionRows, @i)

	SET @StreetName = Field(@Row, "StreetName")
	SET @HouseNumber = Field(@Row, "HouseNumber")
	SET @ZipCode = Field(@Row, "ZipCode")
	SET @City = Field(@Row, "City")

	SET @Address = CONCAT(@HouseNumber, " ", @StreetName, ", ", @ZipCode, " ", @City)

	IF INDEXOF(@Addresses, @Address) == 0 THEN
		SET @Addresses = IIF(EMPTY (@Addresses), CONCAT(@Address), CONCAT(@Addresses, "|", @Address))
	ENDIF

NEXT @i

SET @AddressRows = BuildRowsetFromString(@Addresses, '|')

FOR @i = 1 TO ROWCOUNT(@AddressRows) DO

	SET @AddressRow = Row(@AddressRows, @i)
	SET @AddressFromString = Field(@AddressRow, 1)

	OUTPUT(CONCAT('<dt>',@AddressFromString,'</dt>'))

	FOR @j = 1 TO @DataExtensionRowCount DO
        
		SET @Row = Row(@DataExtensionRows,@j)

		SET @StreetName = Field(@Row, "StreetName")
		SET @HouseNumber = Field(@Row, "HouseNumber")
		SET @ZipCode = Field(@Row, "ZipCode")
		SET @City = Field(@Row, "City")
		SET @Person = Field(@Row, "Person")

		SET @Address = CONCAT(@HouseNumber, " ", @StreetName, ", ", @ZipCode, " ", @City)

		IF @Address == @AddressFromString THEN

			OUTPUT(CONCAT('<dd>',@Person,'</dd>'))

		ENDIF

	NEXT @j

NEXT @i

]%%
</dl>

Have I missed anything?

Please poke me with a sharp comment below or use the contact form.