cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Need to share some code when posting a question or reply? Make sure to use the "Insert code sample" menu option. Learn more! X

How to perform Transpose of Infotable ?

AP_9587236
17-Peridot

How to perform Transpose of Infotable ?

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];
}

}

1 ACCEPTED SOLUTION

Accepted Solutions

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.

 

 

 

View solution in original post

3 REPLIES 3

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:

Sathishkumar_C_0-1663153609806.png

Transposed Data:

Sathishkumar_C_1-1663153665894.png

 

Hope this helps.

 

 

 

Top Tags