Skip to main content
V
VK_1015972010-Marble
10-Marble
March 3, 2023
Solved

Need to order column names of two merged infotable

  • March 3, 2023
  • 1 reply
  • 1316 views

HI Developers,

 

I had to merge two infotables and export them into excel. Code is working fine to merge them and export but it is merging alternate rows of both table.

OEEIssueDelayDescPercentageWasteDegreeSample1Sample2
8.1Issue9.1Heating 1289testtesting

Here we have 2 infotables. one has OEE, Delay, Percentage, Degree. Sample1 and Sample2.. Second has Issue, Desc and Waste

Requirement is to merge it like

IssueDescWasteOEEDelayPercentageDegreeSample1Sample2
IssueHeating128.19.10.1289testtesting

 

Can we order infotables as per our requirement? Please suggest

 

 

 

 

 

 

 

Best answer by Sathishkumar_C

if your field names are fixed, then you go and create a datashape [Not using service].

Assuming both the infotable having 1 record.

var newInfoTable = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
 infoTableName: "InfoTable",
 dataShapeName: "<NEW DATASHAPE NAME>"
});

var table1 = [INFOTABLE DATA];
var table2 = [INFOTABLE DATA];

var newEntry = {};
newEntry.Issue = table1.rows[0].Issue;
newEntry.Desc = table1.rows[0].Desc;
newEntry.Waste = table1.rows[0].Waste;
newEntry.OEE = table2.rows[0].OEE;
newEntry.Delay = table2.rows[0].Delay;
newEntry.Percentage = table2.rows[0].Percentage;
newEntry.Degree = table2.rows[0].Degree;
newEntry.Sample1 = table2.rows[0].Sample1;
newEntry.Sample2 = table2.rows[0].Sample2;

newInfoTable.AddRow(newEntry);

result = newInfoTable;

 

1 reply

S
17-Peridot
Sathishkumar_C17-Peridot
17-Peridot
March 6, 2023

Are you using export with Data Export widget? or any other method?

 

try to do following..

  1. Create a data shape with below fields and order
    Issue Desc Waste OEE Delay Percentage Degree Sample1 Sample2
  2. Read the data from InfoTable1 and InfoTable2 
  3. Insert into new infotable with new datashape
V
VK_1015972010-MarbleAuthor
10-Marble
March 6, 2023
Hi @Sathishkumar_C ,

Thanks for your response.
I am using this below code.

//Code starts
var tableList = [table1, table2];

// Iterate through the fields of each table, adding the fields to the results table if necessary and then setting the values
for(var i = 0; i < tableList.length; i++) {
 var currentTable = tableList[i];
 // Need to convert to json in order to iterate through fields if datashape is not known. If datashape is known, see snippets for potentially simpler solution
 var table_json = Resources["InfoTableFunctions"].ToJSON({table: currentTable});

 for(var key in table_json.dataShape.fieldDefinitions) {
 try { // this line will error if the field doesn't already exist in the results table...
 combined_infotable.dataShape.fieldDefinitions[key]
 } catch(err) { // ...so if it does error, then we need to add it
 combined_infotable.AddField(table_json.dataShape.fieldDefinitions[key]);
 }
 }

 // Now iterate through the values of each table and copy over each row
 for(var j = 0; j < currentTable.rows.length; j++) {
 combined_infotable.AddRow(currentTable.rows[j]);
 }
}
//Code ends
I am using data shape as you have mentioned only. table1 has Issue,Desc, Waste and table 2 has remaining columns.
I checked with loggers till creating datashape, it is making datashape correctly but Second for loop is where it is inserting data alternatively.
Kindly suggest as I am stuck from quite a while now.
S
17-Peridot
Sathishkumar_C17-PeridotAnswer
17-Peridot
March 6, 2023

if your field names are fixed, then you go and create a datashape [Not using service].

Assuming both the infotable having 1 record.

var newInfoTable = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
 infoTableName: "InfoTable",
 dataShapeName: "<NEW DATASHAPE NAME>"
});

var table1 = [INFOTABLE DATA];
var table2 = [INFOTABLE DATA];

var newEntry = {};
newEntry.Issue = table1.rows[0].Issue;
newEntry.Desc = table1.rows[0].Desc;
newEntry.Waste = table1.rows[0].Waste;
newEntry.OEE = table2.rows[0].OEE;
newEntry.Delay = table2.rows[0].Delay;
newEntry.Percentage = table2.rows[0].Percentage;
newEntry.Degree = table2.rows[0].Degree;
newEntry.Sample1 = table2.rows[0].Sample1;
newEntry.Sample2 = table2.rows[0].Sample2;

newInfoTable.AddRow(newEntry);

result = newInfoTable;

 

Terms & ConditionsAccessibility statement