Assigning Null Value records in Iterative Macro

[Puru]

Hi there

Trying to assign Null Category records under named Category records. This looks like Iterative Macro but somehow Multi-row doesn't work.

Set A - each Category should have 5 records. The missing records are in Set B in ascending order. The records from Set B should flow into Set A till each Category in Set A reaches count of 5. So if the Category 1 is missing two records then first 2 records from Set B should flow in Set A under Category 1 and take the name 1 and so on. If the Category in Set A already has 5 records then no addition is required.

Set A records				Set B records
Category	Order	Name	Address	Category	Order	Name	Address
1	6	Arthur	New York	Null	51	Liam	Fort Worth
1	8	John	Los Angeles	Null	57	Emma	San Francisco
1	10	Brandy	Chicago	Null	52	Noah	Charlotte
2	12	Dawn	Houston	Null	67	William	Columbus
2	11	Tony	Phoenix	Null	56	Olivia	Indianapolis
3	14	Daniel	San Antonio	Null	50	Ava	Seattle
3	7	Marshall	Philadelphia	Null	58	James	Denver
3	9	Tery	San Diego	Null	66	Isabella	Washington
3	17	Sherise	Dallas	Null	55	Oliver	Boston
4	15	Maya	San Jose	Null	59	Sophia	El Paso
4	16	Elle	Austin	Null	65	Benjamin	Nashville
5	13	Leone	Jacksonville	Null	72	Mia	Portland
6	19	Jon	Mesa	Null	54	Lucas	Las Vegas
6	21	Jonathan	Kansas	Null	64	Amelia	Oklahoma
6	18	David	Miami	Null	68	Mason	Detroit
6	22	Erin	Raleigh	Null	63	Harper	Memphis
6	20	Mike	Long Beach	Null	71	Megan	Louisville
				Null	53	Logan	Baltimore
				Null	70	Evelyn	Milwaukee
				Null	62	Julie	Albuquerque
				Null	60	Jodie	Tucson
				Null	73	Michael	Fresno
				Null	69	Charles	Sacramento
				Null	61	George	Atlanta
				Null	80	Robert	Oakland

[ggruccio · Accepted solution]

Hi @Puru,

While you can probably build an iterative macro there is often more than one way to approach a problem.

For this one I created a numeric ranking for each of the data rows in Set A using a bit of a workaround (you'll see it's a summed running total based on the value 1).  Knowing each category requires 5 values, I did an append by category to a list of numbers 1-5.  I then created two data streams - one with slots already filled, and one with slots yet to fill. 

If set B is already in ascending order then you can just fill in the missing slots by joining based on record position to the Nulls and unioning back to set A.  The rest is cleanup.

Please see the attached workflow: