Community Tip - Learn all about PTC Community Badges. Engage with PTC and see how many you can earn! X
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];
}
}
Solved! Go to Solution.
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:
Transposed Data:
Hope this helps.
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];
}
}
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);
}
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:
Transposed Data:
Hope this helps.