Set default value in a FOR LOOP when one ROW SET is smaller than another

Question

Trying to build out an email based on a few BuildRowSetFromString calls, but the email is failing because the For Loop code is using the RowCount from one String that has more rows than another BuildRowSetFromString.

We've tried to include some conditional content on the Rows, but it's returning an error - likely because the row doesn't exist for the condition to be met. What we tried:

IF IsNull(Row(@devicelinkrows, @i)) THEN 
    SET @devicelink = "null"
  ELSE  
    SET @devicelinkrow = Row(@devicelinkrows, @i)
    SET @devicelink = field(@devicelinkrow,1)             
ENDIF

We have content that inserts a link when the value is 'null'.

Is it possible to SET a default when the rowcount is lower for one set versus another?

Answer 1

If you want to keep a single rowcount for the max iterative and want it to be the larger one, you will want to add in some conditionals based on rowcount.

For example:

SET @RowcountA = Rowcount(@RowsetA)
SET @RowcountB = Rowcount(@RowsetB)

SET @RowcountCompare = SUBTRACT(@RowcountA, @RowcountB)

IF @RowcountCompare > 0 THEN

    SET @LargerRowset = @RowcountA

ELSE

    SET @LargerRowset = @RowcountB

ENDIF

FOR @i = 1 TO @LargerRowset DO

    IF @i <= @RowcountA THEN

        SET @ItemARow = Row(@RowsetA, @i)
        SET @ItemA = field(@ItemARow,1)          

    ELSE

        SET @ItemA = ''

    ENDIF

    IF @i <= @RowcountB THEN

        SET @ItemBRow = Row(@RowsetB, @i)
        SET @ItemB = field(@ItemBRow,1)          

    ELSE

        SET @ItemB = ''

    ENDIF

NEXT @i

This will make it so it will not do the Row and Field lookup if the iterative (@i) is greater than the row count - preventing the error.

Answer 2

It depends a bit on how the two rowsets are related. If you think of your two rowsets as two rows in a datasheet, underneath each other - Are there gaps in your second rowset at random points throughout the column?

1,1,1,1,1,1,1,1,1
2,2,gap,2,2,2,2,gap

this is pretty hard to solve. You'd want an array, which a "RowsetFromString" never is.

Options I see:

Either fill the gaps with "some value", then you can simply write an if / else check to handle "some value" accordingly inside your loop.

Or sort both rowsets somehow, so the gaps are at the end:

Assuming that the order in both is the same and the only missing rows are sorted towards the end of your second column:

1,1,1,1,1,1,1,1,1
2,2,2,2,2,2,2,gap,gap

One approach could be to loop through your rowsets, and write them into a data extension side by side. Then you can do lookups and empty checks for pairs. This essentially acts as an array.

1 2
1 2
...
1 gap
1 gap

Another approach would be to use rowcount() on both sorted rowsets. Compare the two counts, and use only the smaller rowcount in a first for loop. This will produce all your pairs. Then, from the the smaller rowcount + 1, iterate through the rest of the records (the ones without a match) to the higher rowcount, just setting defaults instead of empty values, or producing an error.

Comparing rowcounts is a simple subtract and logical test.

IF Subtract(rowCount(rowset1),rowCount(rowset2) > 0 THEN

ENDIF