I have some data that has been grouped that I want to disaggregate on load.
The data has many fields like Location,Start Year, Age Grouping, Gender. There is one field called Active Customers, most rows have the value 1; ie there are 1 x customer in that Location with that start year, age grouping, gender etc.
However there are some rows with Active Customer set from 2 all the way up to 17.
Can I create something load script to load the rows where active customer is >1 multiple times and created a unique row id at the same time?
Any thoughts much appreciated.
you could use a While clause together with the IterNo() function like this:
LOAD RowNo() as ID, Location, [Start Year], [Age Grouping], Gender From YourSource While IterNo() <= [Active Customer];
see also: IterNo
hope this helps