I wanted to create a RegionGroups table, and for this I needed a list of all of the regions. So I decided to just put the regions themselves in a temporary table, giving me a concise list to work with instead of working with the original table.
A common solution for generating the temporary table might look like this:
RegionsTemp:
LOAD DISTINCT Region
FROM MyMainDataTable
;
But my assumption was that your main data table has, say, 50 million rows of data but only 100 regions. Scanning all 50 million rows like this to get a list of regions would be inefficient. Fortunately, the fieldvalue() function allows us to access all of the values of a field without referring to any tables, without scanning any rows. The while loop just keeps generating rows of a new table as long as there are still more values of the region field. The final result, then, is a new table with every field value in it, generated very efficiently. Then I could just do my loads from that temporary table instead of from the main data table.
So I guess it was a combination of load speed and separation of responsibility that led me to that approach.
In hind sight, this is probably a better approach for this specific example:
RegionGroups:
LOAD
fieldvalue('Region',iterno()) as RegionGroup
,fieldvalue('Region',iterno()) as Region
AUTOGENERATE 1
WHILE len(fieldvalue('Region',iterno()))
;
CONCATENATE (RegionGroups)
LOAD
'All' as RegionGroup
,Region
RESIDENT RegionGroups
;
I probably didn't think to do it that way at first because I often need more than just two loads concatenated like this, and if you needed a third, you could no longer just simply load resident from the RegionGroups table itself, or you'd get duplication of values. I could also just keep repeating the while loop for every new table I'm concatenating, but I think the while fieldvalue logic is complicated enough that I prefer to only do it once. So I'm just in the habit of generating temporary tables first, even if it wasn't necessary for this example.
