Skip to main content
R
rajee13-Aquamarine
13-Aquamarine
January 23, 2019
Solved

filling null values with previous recorded value in the sql query service

  • January 23, 2019
  • 3 replies
  • 2751 views

Hi,

 

I have some columns which have null, double values. I want to replace the null value with previous reading.I am fetching the data from oracle database.I want to manipulate the result of the sql query service using thingworx.

 

Thanks in advance.

Best answer by CarlesColl

Hello,

 

Here you have a code sample to "fill gaps left":

 

var fillGapsLeft = function(infotable,columnNames) {
 var column;
 var nColumns = columnNames.length;
 var previousValues = {};
 for (var i=0;i<nColumns;i++) {
 previousValues[columnNames[i]] = null;
 }
 for each(row in infotable.rows) {
 for (var i=0;i<nColumns;i++) {
 column = columnNames[i];
 if ((row[column]==null)||(row[column]==undefined)) {
 if (previousValues[column]!=null) {
 row[column] = previousValues[column];
 }
 }
 }
 previousValues = row;
 }
}

var result = fillGapsLeft(originalInfotable,["R","B","Y"]);

3 replies

S
slangleyCommunity Manager
Support
January 23, 2019

Hi @rajee.

 

We're not totally understanding your issue.  Where are the null and double values?  In your Oracle database instance?  Are you trying to move your data to ThingWorx or just displaying the Oracle table in ThingWorx?

 

Please provide further details and we will try to provide a solution.

 

Regards.

 

--Sharon

R
rajee13-AquamarineAuthor
13-Aquamarine
January 25, 2019

I have columns named R,B,Y and each of these columns have values which are null, double. Now, I want to replace the null values with the previous value.I have given the original data in image. Now, i want to change the data in following manner..

1/3/2019 9:20 7.347453 0 0
1/3/2019 9:20 7.347453 0 33.58498
1/3/2019 9:20 7.347453 8.236609 33.58498
1/3/2019 9:21 7.407043 8.236609 33.58498
1/3/2019 9:21 7.407043 8.236609 33.972933
1/3/2019 9:22 7.407043 8.350647 33.972933
1/3/2019 9:23 7.533944 8.350647 33.972933
1/3/2019 9:24 7.533944 8.350647 33.222232
1/3/2019 9:22 7.533944 8.393254 33.222232

I am invoking an sql query service to get this data.I don't want to change the data in oracle instance. Is there a way to manipulate data in above format in thingworx service? 

C
CarlesColl1-VisitorAnswer
1-Visitor
January 25, 2019

Hello,

 

Here you have a code sample to "fill gaps left":

 

var fillGapsLeft = function(infotable,columnNames) {
 var column;
 var nColumns = columnNames.length;
 var previousValues = {};
 for (var i=0;i<nColumns;i++) {
 previousValues[columnNames[i]] = null;
 }
 for each(row in infotable.rows) {
 for (var i=0;i<nColumns;i++) {
 column = columnNames[i];
 if ((row[column]==null)||(row[column]==undefined)) {
 if (previousValues[column]!=null) {
 row[column] = previousValues[column];
 }
 }
 }
 previousValues = row;
 }
}

var result = fillGapsLeft(originalInfotable,["R","B","Y"]);
    Terms & ConditionsAccessibility statement