1

I have a data extension with multiple fields and would like to group the output based on the specific field.

For example the fields on the DE are:

  • Product Type
  • Interest Rate
  • Amount

I would like to group the data outputs based on the product type (rather than repeat the Product Type for each output, for example:

Product Type A

  • Interest Rate 1, Amount 1
  • Interest Rate 2, Amount 2
  • Interest Rate 3, Amount 3

Product Type B

  • Interest Rate 4 Amount 4

etc

I have built the code to output the data without grouping (see below), but not sure how to modify this to achieve what I need.

%%[ 
SET @GroupRows = LookupRows("Product Data Extension", "Reference ID",[Reference ID])  

FOR @counter=1 TO RowCount(@GroupRows)  DO
  SET @currentRow=Row(@GroupRows,@counter)
  SET @Amount=Field(@currentRow, "Amount")  
  SET @Rate=Field(@currentRow, "Rate")  
  SET @ProductType=Field(@currentRow, "Product Type") 
]%% 

/* display output fields */

%%[ NEXT @counter ]%%

Any suggestions would be great. Thanks!

1

You'd just need to keep track of the current Product Type as you're iterating and then output a new heading row when it changes.

For example:

%%[ 
var @i, @rows, @row, @Amount, @Rate, @productType, @prevProductType

SET @rows = LookupRows("Product Data Extension", "Reference ID",[Reference ID])  

set @prevProductType = ""

FOR @i = 1 TO RowCount(@rows) DO
  set @row = Row(@rows,@i)
  set @Amount = Field(@row, "Amount")  
  set @Rate = Field(@row, "Rate")  
  set @ProductType = Field(@row, "Product Type") 

  if empty(@prevProductType) or @productType != @prevProductType then

     set @prevProductType = @productType

]%% 

     <h1>%%=v(@ProductType)=%%</h1>

%%[ else ]%%

     %%=v(@i)=%% %%=v(@Amount)=%% %%=v(@Rate)=%%<br/>

%%[ endif ]%%

%%[ NEXT @i ]%%

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.