Parsing data in the data extension with AMPscript

Question

I have a data extension in Marketing Cloud with data about tasks per account per contact. A contact can be related to more than one account and can have multiple tasks in each account.

I need to send one email to a person with information about tasks in each account. For example contact c1 should receive an email like this:

Tasks in a1:

• task1
• task2
• task3

Tasks in a2:

• task1

I attempted to achieve this in AMPscript via retrieving and a couple of loops, but I would end up outputting duplicate AccountNames

a1

• task 1
• task 2
• task 3

a1

• task 1
• task 2
• task 3

If I could use JavaScript I would iterate the records and put them in a map, but as far as I know Map is not supported by SSJS? I can have a script to prep data first (write to a field), but I'm not sure how to get rid of the duplicates.

Here's my ampscript:

%%[
SET @tasksDE = 'TasksDE'
SET @activities = LOOKUPROWS(@tasksDE, 'ContactId', Id)
SET @rowCount = rowCount(@activities)

IF @rowCount > 0 THEN
    FOR @i = 1 TO @rowCount DO
        SET @row = row(@activities, @i) /*get row*/
        SET @accountName = field(@row, "AccountName")
        SET @accountId = field(@row, "AccountId")
]%%
<p>Shop "%%=v(@accountName)=%%":</p>
%%[
        SET @activitiesPerAccount = LOOKUPROWS(@tasksDE, 'AccountId', @accountId)
        SET @rowCount1 = rowCount(@activitiesPerAccount)

        IF @rowCount1 > 0 THEN
            FOR @j = 1 TO @rowCount1 DO
                SET @row1 = row(@activitiesPerAccount, @j) /*get row*/ 
                SET @taskName = field(@row1, "TaskName")
]%%
<p>%%=v(@taskName)=%%</p>
%%[
            NEXT @j
        ENDIF
    NEXT @i
ENDIF
]%%

I am new to development on Marketing Cloud. Any help is appreciated.

Answer 1

Based on what you provided, I have added some changes so you can get sorted data and use the nested loop for this case.

%%[ 
  SET @deName= "TasksDE"
  SET @Id = AttributeValue("_subscriberkey") 
  SET @initAccountId = ""

  SET @contactRows= LookupOrderedRows(@deName, 0 , "AccountId ASC, TaskName ASC", "ContactId",@Id)
  SET @rowCount = RowCount(@contactRows) 
    IF @rowCount > 0 THEN 
      FOR @i=1 TO @rowCount DO 
        SET @row = row(@contactRows, @i ) /*get row*/ 
        SET @accountName = field(@row,"AccountName") 
        SET @accountId = field(@row,"AccountId")
        IF @initAccountId != @accountId THEN
            SET @initAccountId = @accountId
]%%

<p>Shop "%%=v(@accountName)=%%":</p>

%%[
       SET @tasks= LookupRows(@deName,"ContactId", @Id, "AccountId",@initAccountId) 
       SET @rowCount1= rowCount(@tasks) 
        IF @rowCount1 > 0 THEN 
          FOR @j= 1 TO @rowCount1 DO 
            SET @row1 = row(@tasks,@j) /*get row*/ 
            SET @taskName= field(@row1,"TaskName") 
]%% 

<p>%%=v(@taskName)=%%</p>

%%[NEXT @j ENDIF]%% 
%%[ENDIF]%%
%%[NEXT @i ENDIF]%%

You can try it yourself here: https://mcsnippets.herokuapp.com/s/Gv5necBi

Note: This will work if your "TasksDE" has smaller 2000 records, otherwise you need to enhance it.