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

[rajee]

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.

[slangley]

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

[rajee]

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? 

[CarlesColl · Accepted solution]

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"]);