How to impletement the below sql logic in Alteryx (use either Generate rows or iterative m

Question

" WHILE "
" (SELECT COUNT([Composite role]) FROM #composite_roles WHERE [Iteration] = @count) > 0 "
" BEGIN "
" INSERT INTO #composite_roles "
" SELECT "
" [Base].[Client] "
" ,AGR_AGRS.AGR_NAME AS [Composite role] "
" ,ISNULL(AGR_AGRS.AGR_NAME + ';', '') + [Base].[Role tree] AS [Role tree] "
" ,[Base].[Role] "
" ,@count + 1 AS [Iteration] "
" FROM "
" #composite_roles AS [Base] "
" INNER JOIN [app_sap].AGR_AGRS "
" ON AGR_AGRS.MANDT = [Base].[Client] "
" AND AGR_AGRS.CHILD_AGR = [Base].[Composite role] "
" WHERE "
" [Base].[Iteration] = @count; "
" "
" SET @count = @count + 1; "
" END; "

dreldrel · Answer

Could you explain the main purpose of this query? I can see you also join another table apart from the interative process

MinhLO · Answer

@Celeya

Iterative macros => you can leverage this tutorial to help you :

Alteryx Iterative Macros - YouTube

Then your stopping condition can be a max number of iterations.

The other thing i discovered recently and could be of interest for you is the Make Group tool. Maybe try dedicating a few minutes to see if it suits your needs => could make you win some time .