Need help sorting through external data

[esmith]

I have a hypothetical external data file with the following info (1st line contains field names):

SHAPE         QUANTITY        LOCATION
Diamond       2               Living Room
Diamond       1               Master Bedroom
Diamond       1               Kitchen
Diamond       1               Dining Room
Circle        3               Basement
Circle        2               Master Bedroom
Circle        2               Dining Room
Square        1               Attic
Square        2               Living Room
Rhombus       15              Kitchen
Trapezoid     3               Basement
Trapezoid     2               Foyer
Triangle      3               Dining Room

I would like to create the following arrays of data (with the last array containing a sub-array corresponding to each location in the preceding array) from this information in an OnJobStart rule for referring to in records:

uniqueShapes = ["Diamond","Circle","Square","Rhombus","Trapezoid","Triangle"]
shapeQtys = [5,7,3,15,5,3]
uniqueLocations = ["Living Room","Master Bedroom","Kitchen","Dining Room","Basement","Attic","Foyer"]
shapesPerLocation = [("Diamond","Square"),("Diamond","Circle"),("Diamond","Rhombus"),("Diamond","Circle","Trapezoid"),("Circle","Trapezoid"),("Square"),("Trapezoid")]

My goal would be to reference these global arrays of data from separate text and graphic rules for placing content on the page(s) of my template. The data is generated via a web portal and I would have a unique external data file provided for each record call (which is why I build the name of the external data file in the first line of my OnJobStart rule based on the XML call containing the campaign name).

 

I began to write code in an OnJobStart rule to set up the first array as such:

var fileName = "prefix_ " + Field("campaign") + ".csv";
shapeBreakdown = new ExternalDataFileEx(fileName, ",");
if(shapeBreakdown.valid != true)
{
   Print("Failed to link to the external data file in OnJobStart");
}

uniqueShapes = [];
shapeQtys = [];
uniqueLocations = [];
shapesPerLocation = [];
var unique = "false";

for (var i=1; i<=shapeBreakdown.recordCount; i++) {
   var shape = shapeBreakdown.GetFieldValue(i, "SHAPE");
   var check = 0;
   do {
       if (shape != uniqueShapes[check]) {
           var unique = "true";
       }
   while (check<uniqueShapes.length, check++);
   if (unique == "true") {
       uniqueShapes.push(shape);
   }    
}

Reviewing what I wrote already, I'm thinking there is likely a more efficient way to do this, especially to generate all 4 arrays of information needed. I thought the recent thread on creating a subset of external data might apply, but as I worked through it, it didn't seem to fit my problem exactly. Would anyone care to take a stab at explaining how I could most effectively use JavaScript to generate the data I need from the data I (will be) provided?

[Dan Korn]

Try this:

Shapes = new Object;
Locations = new Object;

for (var record = 1; record <= shapeBreakdown.recordCount; record++)
{
   var shape = shapeBreakdown.GetFieldValue(record, "SHAPE");
   if (!Shapes[shape])
       Shapes[shape] = 0;
   Shapes[shape] += Int(shapeBreakdown.GetFieldValue(record, "QUANTITY"));

   var location = shapeBreakdown.GetFieldValue(record, "LOCATION");
   if (!Locations[location])
       Locations[location] = [];
   Locations[location].push(shape);
}

You can access the data as properties of each object, for instance:

return Shapes["Diamond"];

Or:

return Locations["Basement"];

We're taking advantage of the property "map" of an object to keep track of unique instances. In this case, each property of the Shapes object returns the total quantity, and each property of the Locations object returns an array of all the shapes used (although this array could also be replaced with an object with properties). If you need the list of all the properties, you can iterate with a "for...in" statement, like so:

uniqueLocations = [];
for (var location in Locations)
   uniqueLocations.push(location);

uniqueShapes = [];
for (var shape in Shapes)
   uniqueShapes.push(shape);

The code above creates arrays from the property names, but depending on what you're doing, you could do something else in that "for...in" loop and access the properties directly without needing to create the intermediate array.

[Dan Korn]

My goal would be to reference these global arrays of data from separate text and graphic rules for placing content on the page(s) of my template. The data is generated via a web portal and I would have a unique external data file provided for each record call (which is why I build the name of the external data file in the first line of my OnJobStart rule based on the XML call containing the campaign name).

 

I began to write code in an OnJobStart rule to set up the first array as such:

If you have a new external data file for each record of your main input data, I would think you want to process each external file in OnRecordStart, or just in a regular rule, rather than in OnJobStart.