Need to order column names of two merged infotable

[VK_10159720]

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.

OEE	Issue	Delay	Desc	Percentage	Waste	Degree	Sample1	Sample2
8.1	Issue	9.1	Heating		12	89	test	testing

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

Issue	Desc	Waste	OEE	Delay	Percentage	Degree	Sample1	Sample2
Issue	Heating	12	8.1	9.1	0.12	89	test	testing

Can we order infotables as per our requirement? Please suggest

[Sathishkumar_C]

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

[VK_10159720]

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.

[Sathishkumar_C · Accepted solution]

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;

[Constantine]

Hello,

Here's a concise and efficient way to do this:

[table1, table2].forEach(t => {
    t.AddField({ name: 'id', baseType: 'INTEGER' });
    t.rows.toArray().forEach((r, i) => r.id = i);
});

let result = Resources["InfoTableFunctions"].Intersect({
	columns1: 'OEE,Delay,Percentage,Degree,Sample1,Sample2', 
	columns2: 'Issue,Desc,Waste',
	t1: table1, 
	t2: table2,
	joinColumns1: 'id', 
	joinColumns2: 'id'
});

/ Constantine