Solved

Javascript List of Data to Crosstab

Posted on 2011-02-27
16
1,283 Views
Last Modified: 2012-05-11
Hello,

I have a list of data such as:

John     Apples    8
John     Pears   4
Jane     Apples    3
Jane     Oranges   2

How can I either use JavaScript or jQuery to create an array that reads more like a crosstab ala:

Person    Apples    Pears    Oranges
John        8              4
Jane        3                            2

I have a feeling that something like this might be close:

http://www.eggheadcafe.com/community/aspnet/3/74751/build-a-table.aspx

The reason being my data is stored in a simple 3 column list like I showed, but I am using highcharts.com's charting solutions and these require more of a matrix formatted CSV or other data like shown above whereby each person is on it's own row and there is a column for either type of fruit.

This is definitely doable as this is just a matter of parsing data, I just can't think of how to do it!

Thanks!
0
Comment
Question by:eviglotti
  • 9
  • 7
16 Comments
 
LVL 14

Expert Comment

by:R-Byter
ID: 34995957
You should go with JSON formatted data.
In your case it would be:

{
     "John": {"Apples":8, "Pears":4, "Oranges":null},
     "Jane": {"Apples":3, "Pears":null, "Oranges":2}
}

Open in new window


Good info on using it in javascript:

http://www.json.org/js.html

Regards
0
 

Author Comment

by:eviglotti
ID: 34999711
Sorry, I don't follow. I can't control how the data comes INTO this stuff, it has to come in like this:

John     Apples    8
John     Pears   4
Jane     Apples    3
Jane     Oranges   2

So NOW, how do I parse it to make it INTO something? Maybe what you were going after is that I can convert the above into JSON formatted data and then from THERE, I'm in good shape, but how do I get to that point? How do I convert the above list of data into the JSON formatted data?

Thanks!
0
 
LVL 14

Expert Comment

by:R-Byter
ID: 35005959
Where that data comes from? Can you post that part of code? Does it come from txt file, coma separated values, what?
Sorry for late response, I forgot about your question.

Regards
0
 

Author Comment

by:eviglotti
ID: 35008878
Sorry, it actually comes from a SQL table, so basically I would be doing "select * from tableofvalues". I have abstraction layers to get it from SQL, but basically yeah, it is from SQL.

Thanks.
0
 
LVL 14

Expert Comment

by:R-Byter
ID: 35010759
Since I still don't know how you serve that data to javascript i wrote this code as a description and not working solution.

<script language="javascript">
function person() {
	this.Name = null;
	this.Apples = null;
	this.Oranges = null;
	this.Pears = null;
}

var c = 0;
var old = "";
var persons = [];

for (row in recordset)
{
	//looping throgh recordset and creating new person everytime the person name changes
	//once new person is created looping continues to fill data for apples, oranges and pears
	if (old != row[0])
	{
		persons[c] = new person();
	}
	person[c].Name = row[0];
	switch (row[1])
	{
		case "Apples":
			person[c].Apples = row[2];
			break;
		case "Oranges":
			person[c].Oranges = row[2];
			break;
		case "Pears":
			person[c].Pears = row[2];
			break;
	}
	old = row[0];
	c++;
}
</script>

Open in new window


After that you can loop through persons array and write every data in table cells. Something like this:

document.write(persons[0].Apples) - which would write 8 for John
persons[0] name is set to John

etc..

If you need any further info do not hesitate to ask.
But I must say that I would prefer creating json just after recordset populating. There is an easy way to do that in php for example with the help of json_encode function. Then retreve it in javascript via AJAX.

Regards
0
 

Author Comment

by:eviglotti
ID: 35010852
Thanks much. Sorry for the black box on this. It doesn't matter too much, but this is actually going to be used by a SharePoint page. And that sharepoint page will probably use jQuery and various things to pull data from a list in sharepoint. So I believe that the data will end up looking very much like what I had mentioned originally (i.e. Person name, item type, count of items)

I think the only kicker on your solution is that I don't know going into this that the only Item Types in question are apples, oranges and pears. They could be anything.

Basically if you visualize what I'm doing, it's basically the equivalent of having an Excel spreadsheet that looks like this:

Person Name    Item Type    Count of items
John     Apples    8
John     Pears   4
Jane     Apples    3
Jane     Oranges   2

And doing an Excel pivot table and doing person as the rows, and item type as the column and count of items as the measure. Just like in Excel's pivot tables, it won't know what all the item types are.

Thanks!
0
 
LVL 14

Expert Comment

by:R-Byter
ID: 35013093
I understand. How about You get all different Item Types from sql query with SELECT DISTINCT...? It could provide you a starting point for any of possible solutions.
0
 

Author Comment

by:eviglotti
ID: 35013223
Good point, yeah, I will definitely be able to do that. So consider that I can run a SQL distinct clause to determine all the apples, oranges and pears categories.

I see now how this could help, but could definitely help with a tangible example if you could.

Thanks!
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 14

Expert Comment

by:R-Byter
ID: 35013362
Just to be clear, you can have not only apples, pears and oranges but some others too, like plums, peaches etc?
0
 

Author Comment

by:eviglotti
ID: 35013389
Yup! But you're right, you'll know the universe of options when running this output by me first running the select distinct thing.

So you can almost pretend like I have two text files, one that lists out all the distinct fruits and then another text file with the list data in my original post:

John     Apples    8
John     Pears   4
Jane     Apples    3
Jane     Oranges   2
Jane     Plums      5

Thanks!
0
 
LVL 14

Expert Comment

by:R-Byter
ID: 35013479
Will write the solution for you but tonight. :)
I have to go to sleep, will continue tomorrow if thats ok.

Regards
0
 

Author Comment

by:eviglotti
ID: 35013587
Totally, thanks!
0
 
LVL 14

Expert Comment

by:R-Byter
ID: 35019642
I'm working again on the solution. Please be patient.

Regards
0
 
LVL 14

Accepted Solution

by:
R-Byter earned 250 total points
ID: 35023633
Here you go, sorry for the delay. You can see the code below:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Javascript List of Data to Crosstab</title>
<script src="https://www.google.com/jsapi"></script>
<script>
google.load('jquery', '1.5.1');
</script>
<script type="text/javascript">
$(function() {
	//define person object
	var person = {
		"Name": null
	};
	//get list of categories(fruits( appearing in your data
	$.get('categories.txt', function(data) {
		var categories = [];
		categories = data.split("\n");
		for (category in categories)
		{
			//adding those categories to person object and stripping extra whitespace
			categories[category] = categories[category].replace(/^\s+|\s+$/g,'').replace(/\s+/g,' ');
			person[categories[category]] = null;
			//writing header of table (should be replaced with table html tags)
			document.write("-"+categories[category]+"-");
		}
		document.write("<br /><br />");
		
		var c = 0;
		var old = "";
		var persons = [];
		
		//get list of names and associated amounts in each category
		$.get('persons.txt', function(data) {
			var recordset = [];
			recordset = data.split("\n");
			for (record in recordset)
			{
				//removing extra spaces from each line
				recordset[record] = recordset[record].replace(/^\s+|\s+$/g,'').replace(/\s+/g,' ');
				//create array of elements inside each line, split by space
				recordset[record] = recordset[record].split(" ");
				//whenever person name changes while browsing through recordset, create new person and fill it with proper values from current line
				if (old != recordset[record][0])
				{
					persons[c] = jQuery.extend(true, {}, person);
					persons[c].Name = recordset[record][0];
					c++;
				}
				persons[c-1][recordset[record][1]] = recordset[record][2];
				old = recordset[record][0];
			}
			
			//output names in desired format (should be replaced with table html tags)
			for (i=0;i<c;i++)
			{
				for (var key in persons[i]) {
					document.write("-"+persons[i][key]+"-");
				}
				document.write("<br />");
			}
		});
		
	});
	
});
</script>
</head>

<body>
</body>
</html>

Open in new window


Or you can see live example here:

http://www.bosko.rs/ee/crosstab/

I'm attaching two txt files called in the code, categories.txt and persons.txt. Feel free to ask if you need any further explanation.

crosstab.zip

Regards
0
 

Author Closing Comment

by:eviglotti
ID: 35024376
This is perfect, thank you!
0
 
LVL 14

Expert Comment

by:R-Byter
ID: 35024930
You're welcome and thanks for the points.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In my daily work (mainly using ASP.net), I need to write a lot of JavaScript code. One of the most repetitive tasks I do are the jQuery Ajax calls. You know: (CODE) I don't know if for you it's the same, but for me is soooo tedious to write the …
JavaScript can be used in a browser to change parts of a webpage dynamically. It begins with the following pattern: If condition W is true, do thing X to target Y after event Z. Below are some tips and tricks to help you get started with JavaScript …
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now