Skip to main content
A
AP_958723617-Peridot
17-Peridot
September 13, 2022
Solved

How to perform Transpose of Infotable ?

  • September 13, 2022
  • 1 reply
  • 1868 views

How to perform Transpose of Infotable ? Row values should be added as Column Values and vice versa. 

I performed the following code. But it is not working. 
for(let i=0;i<Input.length;i++){
for(let j=0;j<finalResult.length;j++){
finalResult[j][i] = Input[i][j];
}

}

Best answer by Sathishkumar_C

Example for ROW to COLUMN transpose:

 


// CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE(transpose_ds)
let sourceTable = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
 infoTableName: "InfoTable",
 dataShapeName: "transpose_ds"
});
let newEntry = {
 f1: '1',// STRING
 f2: 'a',// STRING
 f3: 'A'// STRING
};
sourceTable.AddRow(newEntry);
newEntry = {
 f1: '2',// STRING
 f2: 'b',// STRING
 f3: 'B'// STRING
};
sourceTable.AddRow(newEntry);
newEntry = {
 f1: '3',// STRING
 f2: 'c',// STRING
 f3: 'C'// STRING
};
sourceTable.AddRow(newEntry);
newEntry = {
 f1: '4',// STRING
 f2: 'd',// STRING
 f3: 'D'// STRING
};
sourceTable.AddRow(newEntry);

let transposedTable = Resources["InfoTableFunctions"].CreateInfoTable();
var stRecordCount = sourceTable.length;
var transposedTableFields = [];
for(var f = 0; f < stRecordCount; f++){
 var fieldName = 'trn_f' + (sourceTable.rows[f].f1);
 let newField = {
 name: fieldName,
 baseType: 'STRING'
 };
 transposedTable.AddField(newField);
 transposedTableFields.push(fieldName);
}

for(var i = 0; i < stRecordCount; i++){
	var sourceRowData = sourceTable.rows[i];
 
 /** convert as colum data **/
 var row = 0;
 for(var rowValue in sourceRowData) {
 var transposed1stFieldName = transposedTableFields[i];
 if(i == 0){
 var dataEntry0 = {};
 dataEntry0[transposed1stFieldName] = sourceRowData[rowValue];
 transposedTable.AddRow(dataEntry0);
 }else{
 transposedTable.rows[row][transposed1stFieldName] = sourceRowData[rowValue];
 row++;
 }
 }
}
result = sourceTable;
result = transposedTable;

 

 

 

Source Data:

Sathishkumar_C_0-1663153609806.png

Transposed Data:

Sathishkumar_C_1-1663153665894.png

 

Hope this helps.

 

 

 

1 reply

S
17-Peridot
Sathishkumar_C17-Peridot
17-Peridot
September 13, 2022

Add/Remove dynamic field in the 'finalResult' infotable and then you add the values.

for(let i=0;i<Input.length;i++){
 for(let j=0;j<finalResult.length;j++){
 /** validate new fields if required add new fields HERE**/
 finalResult[j][i] = Input[i][j];
 }
}
A
AP_958723617-PeridotAuthor
17-Peridot
September 13, 2022

This is my code. It is not working in LineNumber 13. where "if(TestRequestData[i][j]) is written".

Error is -"Java class "com.thingworx.types.collections.ValueCollection" has no public instance field or method named "4". "

 

JS code:

let finalResult = DataShapes["SmartParts.TestRequestSeries"].CreateValues();
let result = DataShapes["SmartParts.TestRequestSeries"].CreateValues();
try{
if(TestRequestData.length > 0){
for(let k=0;k<5;k++){
finalResult.AddRow({});
}
logger.debug("TestRequestGraph: finalResult1 len is "+finalResult.length);

for(let i=0;i<TestRequestData.length;i++){
for(let j=0;j<finalResult.length;j++){
try{
if(TestRequestData[i][j]){
let dateTime = TestRequestData[i][j];
finalResult[j][i] = new Date(dateTime);
}
else
finalResult[j][i] = null;
}catch(err){
logger.warn("TestRequestGraph: empty cell warning in TestRequestData["+i+"]["+j+"]");
}
}
}
}
result = finalResult;
}catch(e){
logger.warn("TestRequestGraph: error is "+e+" in linenumber "+e.lineNumber);
}

S
17-Peridot
Sathishkumar_C17-PeridotAnswer
17-Peridot
September 14, 2022

Example for ROW to COLUMN transpose:

 


// CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE(transpose_ds)
let sourceTable = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
 infoTableName: "InfoTable",
 dataShapeName: "transpose_ds"
});
let newEntry = {
 f1: '1',// STRING
 f2: 'a',// STRING
 f3: 'A'// STRING
};
sourceTable.AddRow(newEntry);
newEntry = {
 f1: '2',// STRING
 f2: 'b',// STRING
 f3: 'B'// STRING
};
sourceTable.AddRow(newEntry);
newEntry = {
 f1: '3',// STRING
 f2: 'c',// STRING
 f3: 'C'// STRING
};
sourceTable.AddRow(newEntry);
newEntry = {
 f1: '4',// STRING
 f2: 'd',// STRING
 f3: 'D'// STRING
};
sourceTable.AddRow(newEntry);

let transposedTable = Resources["InfoTableFunctions"].CreateInfoTable();
var stRecordCount = sourceTable.length;
var transposedTableFields = [];
for(var f = 0; f < stRecordCount; f++){
 var fieldName = 'trn_f' + (sourceTable.rows[f].f1);
 let newField = {
 name: fieldName,
 baseType: 'STRING'
 };
 transposedTable.AddField(newField);
 transposedTableFields.push(fieldName);
}

for(var i = 0; i < stRecordCount; i++){
	var sourceRowData = sourceTable.rows[i];
 
 /** convert as colum data **/
 var row = 0;
 for(var rowValue in sourceRowData) {
 var transposed1stFieldName = transposedTableFields[i];
 if(i == 0){
 var dataEntry0 = {};
 dataEntry0[transposed1stFieldName] = sourceRowData[rowValue];
 transposedTable.AddRow(dataEntry0);
 }else{
 transposedTable.rows[row][transposed1stFieldName] = sourceRowData[rowValue];
 row++;
 }
 }
}
result = sourceTable;
result = transposedTable;

 

 

 

Source Data:

Sathishkumar_C_0-1663153609806.png

Transposed Data:

Sathishkumar_C_1-1663153665894.png

 

Hope this helps.

 

 

 

Terms & ConditionsAccessibility statement