AmpScript lookup function: Display data from multiple columns in a data extension?

Question

I'm creating an email to display subscriber's order details. All the data is stored in one spreadsheet/data extension. The columns include OrderID1, OrderType1, OrderID2, OrderType2, OrderID3, OrderType3, ..., OrderID20, OrderType20. The OrderIDs will be empty if there are none. There are only 2 possible values for Order Type: Type A and Type B.

The email is going to be displayed as:

Dear customer,

Here are your recent order details:

Order Type A

Order ID: OrderID1 Order Date: OrderDate1

Order ID: OrderID2 Order Date: OrderDate2

......

Order Type B

Order ID: OrderID3 Order Date: OrderDate3

Order ID: OrderID4 Order Date: OrderDate4

......

Until this subscriber has no more Orders to display.

I'm currently doing this in a very repetitive way, but I'm sure there is an easier way to handle the looping.

Below is my code. As you can see it's really repetitive. Imagine having to copy and paste 20 times for each order type... Please help. Thank you!

%%[
Var @OrderID1, Set @OrderID1 = [OrderID1]
Var @OrderType1, Set @OrderType1 = [OrderType1]
Var @OrderDate1, Set @OrderDate1 = [OrderDate1]


Var @OrderID2, Set @OrderID2 = [OrderID2]
Var @OrderType2, Set @OrderType2 = [OrderType2]
Var @OrderDate2, Set @OrderDate2 = [OrderDate2]

Var @OrderID3, Set @OrderID3 = [OrderID3]
Var @OrderType3, Set @OrderType3 = [OrderType3]
Var @OrderDate3, Set @OrderDate3 = [OrderDate3]

Var @OrderID4, Set @OrderID4 = [OrderID4]
Var @OrderType4, Set @OrderType4 = [OrderType4] 
Var @OrderDate4, Set @OrderDate4 = [OrderDate4]
]%%






Order Type A



%%[if @OrderType1 =="A" then]%%


Order ID: %%=v(@OrderID1)=%% 

Order Date: %%=v(@OrderDate1)=%% 


%%[endif]%%

%%[if not empty (@OrderID2) and (@OrderType2 =="A") then]%%


Order ID: %%=v(@OrderID2)=%% 

Order Date: %%=v(@OrderDate2)=%% 


%%[endif]%%

%%[if not empty (@OrderID3) and (@OrderType3 =="A") then]%%


Order ID: %%=v(@OrderID3)=%% 

Order Date: %%=v(@OrderDate3)=%% 


%%[endif]%%

%%[if not empty (@OrderID4) and (@OrderType4 =="A") then]%%


Order ID: %%=v(@OrderID4)=%% 

Order Date: %%=v(@OrderDate4)=%% 


%%[endif]%%








Order Type B



%%[if @OrderType1 =="B" then]%%


Order ID: %%=v(@OrderID1)=%% 

Order Date: %%=v(@OrderDate1)=%% 


%%[endif]%%

%%[if not empty (@OrderID2) and (@OrderType2 =="B") then]%%


Order ID: %%=v(@OrderID2)=%% 

Order Date: %%=v(@OrderDate2)=%% 


%%[endif]%%

%%[if not empty (@OrderID3) and (@OrderType3 =="B") then]%%


Order ID: %%=v(@OrderID3)=%% 

Order Date: %%=v(@OrderDate3)=%% 


%%[endif]%%

%%[if not empty (@OrderID4) and (@OrderType4 =="B") then]%%


Order ID: %%=v(@OrderID4)=%% 

Order Date: %%=v(@OrderDate4)=%% 


%%[endif]%%

    

Edit: Tried this loop with no luck

 
%%[
 VAR @ThisOrderID, @ThisOrderType, @ThisOrderDate,
 FOR @i = 1 to 20 DO
 SET @ThisOrderID = CONCAT ("OrderID",@i) /Field Name: OrderID1, OrderID2, .../
 SET @ThisOrderType = CONCAT ("OrderType",@i) /Field Name: OrderType1, OrderType2, .../
 SET @ThisOrderDate = CONCAT ("OrderDate",@i) /Field Name: OrderDate1, OrderDate2, .../
 ]%%


%%[IF not empty (@OrderID) AND @OrderType =="A" then]%%

%%v=(@ThisOrderID)=%%
 %%v=(@ThisOrderType)=%%
 %%v=(@ThisOrderDate)=%%

%%[endif]%%

%%[ next @i]%%    

Answer 1

You’ll need to use a for loop. Look at this AMPscript 401 documentation for an example.

for @i = 1 to RowCount(@rows) do

/* Set the row to the row we're currently looping through */
Set @row = Row(@rows,@i)

/* Set some variables */
Set @prodID = Field(@row,"ProductID")
Set @prodName = Field(@row,"ProductName")
Set @prodDesc = Field(@row,"Description")
Set @price = Field(@row,"Price")
Set @totalPurchases = Add(@totalPurchases,Field(@row,"Price"))

box */ ]%%
%%= TreatAsContent(ContentAreaByName("my contents\101-401\RecentPurchases")) =%%
%%[ next @i ]%%