Datamapper: XML data coming in.
Design: I have a detail table that has multiple lines, some of which are for the same field name just different amounts.
Example:
Detail Table Field “Name” Detail Table Field “Total”
Bob 100
Bob 50
Bob 125
Sue 25
Joe 60
Joe 500
I wish to display the Name field once and calculate each line to only show the total amount.
Example:
Detail Table Field “Name” Detail Table Field “Total”
Bob 275
Sue 25
Joe 560
https://learn.objectiflune.com/qa-blobs/11487854497271140615.ol-datamapper
Attached is a sample that shows almost exactly this. The basic idea is that you’ll have a loop looking at each line. At each line, it checks a series of conditions. Then it updates a total count (see the action script in each condition) and adds an item to the appropriate detail table.
Thank you, this is good information, but my detail lines are variable names. I am unable to look at static line description because the detail lines in my file could be Bob, Sue and Joe today and tomorrow they may be Tim and Carol. I also do not want to have a detail table that shows all records. I would like to only show the same name once and add all their lines together to show the total once.
Does this make more sense?
Yep. In your case, rather than compare to static line descriptions, you’ll want to compare to the previous node, assuming they’re in order by name. So you’d store each one in a variable and when it gets to the next one, it would compare the current to the previous. If it’s the same, it keeps incrementing the total value. If it’s new, it makes a new extraction to set a new name in the detail and then starts incrementing the total in that new detail line.
You’ll probably have a lot more work to get this running than is strictly displayed in that example, but it should give you the building blocks you’ll need to piece it together.
An idea would be to first generate a detail table as you would nomal do with the Repeat step. Then use the below script to:
if(record.tables.detail){
var sortBy = record.tables.detail[0].fields.name;
var uniqueClientsTotal = sumSameMetrics(sortDetailTable(sortBy));
//logger.info(uniqueClientsTotal);
}
//SORT Detail Table
function sortDetailTable(sortParam){
let sortedDetailRecords=[];
let detailRecordsCount = record.tables.detail.length;
let detailFieldsCount = record.tables.detail[0].fields.length;
let keyDetailIndex;
sortParam = record.tables.detail[0].fields['name'];
for(let i=0; i<detailRecordsCount; i++){
let detailFields = [];
for(let j=0; j<detailFieldsCount; j++){
if(record.tables.detail[i].fields[j] == sortParam){
keyDetailIndex = j;
}
detailFields.push(record.tables.detail[i].fields[j]);
}
sortedDetailRecords.push(detailFields);
detailFields =[];
}
sortedDetailRecords.sort(function(a,b){
if(a[keyDetailIndex].toLowerCase() < b[keyDetailIndex].toLowerCase()) return -1;
if(a[keyDetailIndex].toLowerCase() > b[keyDetailIndex].toLowerCase()) return 1;
return 0;
});
return sortedDetailRecords;
}
// SUM each unique detail record's total
function sumSameMetrics(arr) {
var sameMetrics = [];
for (var i = 0; i < arr.length-1; i++) {
if ((arr[i][0] === arr[i + 1][0] && sameMetrics.indexOf(arr[i][0]) < 0) || sameMetrics.indexOf(arr[i][0]) < 0 ) {
sameMetrics.push(arr[i][0]);
}
}
//logger.info(sameMetrics);
sameMetrics = sameMetrics.map(function(a){ return [a, 0];});
//logger.info(sameMetrics);
for (i = 0; i < arr.length; i++) {
for (var j = 0; j < sameMetrics.length; j++) {
if (arr[i][0] === sameMetrics[j][0]) {
sameMetrics[j][1] += Number(arr[i][1]);
}
}
}
return sameMetrics;
}
Once you have the array, it is now just a matter of following the Create a Detail Table from a Linear Data Structure guide to then create a new detail table with unique names and corresponding sums.
Click here to download Example Data Mapping Config File.
As much as I would like to advise the GUI, I see no easy solution here except running a script in the Action step to generate the details records in the way that you want.I will see if I can write an example script showing how you could get there…
