Pivot JSON table

[Fabio]

I have the following JSON table:

[{"varcode":1,"dtregistro":"2014-12-04 15:00:00","varvalue":"0.91590","valueinfo":null,"varpath":"Teste\/Teste 1","vardesc":"Descricao do teste 1","varprefix":"","varsufix":" \u00baC"},
{"varcode":2,"dtregistro":"2014-12-04 15:00:00","varvalue":"0.63449","valueinfo":null,"varpath":"Teste\/Teste 2","vardesc":"Descricao do teste 2","varprefix":"","varsufix":" litros"},
{"varcode":3,"dtregistro":"2014-12-04 15:00:00","varvalue":"0.87255","valueinfo":null,"varpath":"Teste\/Teste 3","vardesc":"Descricao do teste 3","varprefix":"","varsufix":" V"},
{"varcode":1,"dtregistro":"2014-12-04 15:00:01","varvalue":"0.63558","valueinfo":null,"varpath":"Teste\/Teste 1","vardesc":"Descricao do teste 1","varprefix":"","varsufix":" \u00baC"},
{"varcode":2,"dtregistro":"2014-12-04 15:00:01","varvalue":"0.29574","valueinfo":null,"varpath":"Teste\/Teste 2","vardesc":"Descricao do teste 2","varprefix":"","varsufix":" litros"},
{"varcode":3,"dtregistro":"2014-12-04 15:00:01","varvalue":"0.25975","valueinfo":null,"varpath":"Teste\/Teste 3","vardesc":"Descricao do teste 3","varprefix":"","varsufix":" V"},
{"varcode":1,"dtregistro":"2014-12-04 15:00:02","varvalue":"0.19308","valueinfo":null,"varpath":"Teste\/Teste 1","vardesc":"Descricao do teste 1","varprefix":"","varsufix":" \u00baC"},
{"varcode":2,"dtregistro":"2014-12-04 15:00:02","varvalue":"0.49601","valueinfo":null,"varpath":"Teste\/Teste 2","vardesc":"Descricao do teste 2","varprefix":"","varsufix":" litros"},
{"varcode":3,"dtregistro":"2014-12-04 15:00:02","varvalue":"0.32496","valueinfo":null,"varpath":"Teste\/Teste 3","vardesc":"Descricao do teste 3","varprefix":"","varsufix":" V"},
{"varcode":1,"dtregistro":"2014-12-04 15:00:03","varvalue":"0.13569","valueinfo":null,"varpath":"Teste\/Teste 1","vardesc":"Descricao do teste 1","varprefix":"","varsufix":" \u00baC"},
{"varcode":2,"dtregistro":"2014-12-04 15:00:03","varvalue":"0.51165","valueinfo":null,"varpath":"Teste\/Teste 2","vardesc":"Descricao do teste 2","varprefix":"","varsufix":" litros"},
{"varcode":3,"dtregistro":"2014-12-04 15:00:03","varvalue":"0.14491","valueinfo":null,"varpath":"Teste\/Teste 3","vardesc":"Descricao do teste 3","varprefix":"","varsufix":" V"},
{"varcode":1,"dtregistro":"2014-12-04 15:00:04","varvalue":"0.94008","valueinfo":null,"varpath":"Teste\/Teste 1","vardesc":"Descricao do teste 1","varprefix":"","varsufix":" \u00baC"},
{"varcode":2,"dtregistro":"2014-12-04 15:00:04","varvalue":"0.43365","valueinfo":null,"varpath":"Teste\/Teste 2","vardesc":"Descricao do teste 2","varprefix":"","varsufix":" litros"},
{"varcode":3,"dtregistro":"2014-12-04 15:00:04","varvalue":"0.57154","valueinfo":null,"varpath":"Teste\/Teste 3","vardesc":"Descricao do teste 3","varprefix":"","varsufix":" V"},
{"varcode":1,"dtregistro":"2014-12-04 15:00:05","varvalue":"0.48336","valueinfo":null,"varpath":"Teste\/Teste 1","vardesc":"Descricao do teste 1","varprefix":"","varsufix":" \u00baC"},
{"varcode":2,"dtregistro":"2014-12-04 15:00:05","varvalue":"0.79085","valueinfo":null,"varpath":"Teste\/Teste 2","vardesc":"Descricao do teste 2","varprefix":"","varsufix":" litros"},
{"varcode":3,"dtregistro":"2014-12-04 15:00:05","varvalue":"0.49340","valueinfo":null,"varpath":"Teste\/Teste 3","vardesc":"Descricao do teste 3","varprefix":"","varsufix":" V"},
{"varcode":1,"dtregistro":"2014-12-04 15:00:06","varvalue":"0.19485","valueinfo":null,"varpath":"Teste\/Teste 1","vardesc":"Descricao do teste 1","varprefix":"","varsufix":" \u00baC"},
{"varcode":2,"dtregistro":"2014-12-04 15:00:06","varvalue":"0.77752","valueinfo":null,"varpath":"Teste\/Teste 2","vardesc":"Descricao do teste 2","varprefix":"","varsufix":" litros"},
{"varcode":3,"dtregistro":"2014-12-04 15:00:06","varvalue":"0.81419","valueinfo":null,"varpath":"Teste\/Teste 3","vardesc":"Descricao do teste 3","varprefix":"","varsufix":" V"},
{"varcode":1,"dtregistro":"2014-12-04 15:00:07","varvalue":"0.04247","valueinfo":null,"varpath":"Teste\/Teste 1","vardesc":"Descricao do teste 1","varprefix":"","varsufix":" \u00baC"},
{"varcode":2,"dtregistro":"2014-12-04 15:00:07","varvalue":"0.22614","valueinfo":null,"varpath":"Teste\/Teste 2","vardesc":"Descricao do teste 2","varprefix":"","varsufix":" litros"},
{"varcode":3,"dtregistro":"2014-12-04 15:00:07","varvalue":"0.36918","valueinfo":null,"varpath":"Teste\/Teste 3","vardesc":"Descricao do teste 3","varprefix":"","varsufix":" V"}]

And I want another JSON object, with the following characteristics:

** values of dtregistro as lines
** values of vardesc as columns
** Value of varvalue as value.

I searched in examples, but many examples are broken. So, how proceed to get this?

[Yeray]

Hi Fabio,

And I want another JSON object, with the following characteristics:

** values of dtregistro as lines
** values of vardesc as columns
** Value of varvalue as value.

I'm not sure to understand the requirements. Are lines, columns and value supposed to be 3 different series?
dtregistro seems to be a date, vardesc is a string and varvalue is a double; so a Line series could fit well in this data. Is this what you are trying to do?

I searched in examples, but many examples are broken. So, how proceed to get this?

What concrete examples do you find broken?

[Fabio]

In the resultant pivot table, the value of these fields will be:

** values of dtregistro as "rows"
** values of vardesc as columns
** Value of varvalue as value.

a example:

Code: Select all

Data/Hora           | Descricao do teste 1 | Descricao do teste 2 | Descricao do teste 3
04/12/2014 14:30:53 |              0.54881 |              0.59284 |              0.71519
04/12/2014 14:30:54 |              0.42365 |              0.62356 |              0.64589
04/12/2014 14:30:55 |              0.96366 |              0.27266 |              0.38344
04/12/2014 14:30:56 |              0.56804 |              0.39278 |              0.92560
04/12/2014 14:30:57 |              0.02022 |              0.36824 |              0.83262
04/12/2014 14:30:58 |              0.97862 |              0.47361 |              0.79916
04/12/2014 14:30:59 |              0.11827 |              0.72063 |              0.63992
04/12/2014 14:31:00 |              0.52185 |              0.10591 |              0.41466
04/12/2014 14:31:01 |              0.45615 |              0.21655 |              0.56843

[Yeray]

Hi Fabio,

In the resultant pivot table, the value of these fields will be:

** values of dtregistro as "rows"
** values of vardesc as columns
** Value of varvalue as value.

Thanks but I still don't understand what resultant chart would you expect. Something like this?

2014-12-09_1212.png (70.97 KiB) Viewed 14796 times

Code: Select all

var Chart1;

function draw() {
  Chart1=new Tee.Chart("canvas1");

  var series1, series2, series3;
  
  series1 = Chart1.addSeries(new Tee.Line());
  series2 = Chart1.addSeries(new Tee.Line());
  series3 = Chart1.addSeries(new Tee.Line());
  
  series1.title = "Descricao do teste 1";
  series2.title = "Descricao do teste 2";
  series3.title = "Descricao do teste 3";
  
  var startDate, tmp, values1, values2, values3;
  startDate = new Date(2014,12,4,14,30,53);
  
  values1 = [0.54881, 0.42365, 0.96366, 0.56804, 0.02022, 0.97862, 0.11827, 0.52185, 0.45615];
  values2 = [0.59284, 0.62356, 0.27266, 0.39278, 0.36824, 0.47361, 0.72063, 0.10591, 0.21655];
  values3 = [0.71519, 0.64589, 0.38344, 0.92560, 0.83262, 0.79916, 0.63992, 0.41466, 0.56843];
  
  series1.data.values.length = values1.length;
  series2.data.values.length = values2.length;
  series3.data.values.length = values3.length;
  
  series1.data.values = values1;
  series2.data.values = values2;
  series3.data.values = values3;
  
  series1.data.x=new Array(values1.length);
  series2.data.x=new Array(values2.length);
  series3.data.x=new Array(values3.length);
  
  for (i=0; i< values1.length; i++) {
    tmp=new Date(startDate.getTime() + i * 1000);
	series1.data.x[i] = tmp;
	series2.data.x[i] = tmp;
	series3.data.x[i] = tmp;
  }
  
  Chart1.axes.bottom.labels.dateFormat = "mm:ss";
  Chart1.axes.bottom.increment = series1.data.x[1] - series1.data.x[0];
  
  Chart1.draw();
}

[Fabio]

Yes, this is the chart that I want. But, before this (and this is my problem), I need transform my JSON table and if I'm not wrong, using this (http://steema.com/entry/133/Introducing ... ot_library)

So, In resume, I need transform the first table (see it in the first message) into the second table (see it here http://www.teechart.net/support/posting ... 19#pr67971) and in addition to plot the chart, I need show the transformed data on a table (made in JS/HTML by me). So I need to know how access the transformed data. Do you understand-me?

[Yeray]

Hi Fabio,

Yes, this is the chart that I want. But, before this (and this is my problem), I need transform my JSON table and if I'm not wrong, using this (http://steema.com/entry/133/Introducing ... ot_library)

I've corrected the link in the "Demos" tab in the product page to point to the latest version of the Tee.Data demos.

So, In resume, I need transform the first table (see it in the first message) into the second table (see it here http://www.teechart.net/support/posting ... 19#pr67971) and in addition to plot the chart, I need show the transformed data on a table (made in JS/HTML by me). So I need to know how access the transformed data. Do you understand-me?

As an initial step, this is what I've achieved:

test1.zip

(1.31 KiB) Downloaded 1222 times