[Syngenetic]

macosxnerd101, on 04 July 2012 - 08:20 PM, said:

It's still not clear what the data models. Again- focus on an OO model.

Second, if the Excel spreadsheet contains that many rows, it's time to look at importing it into a relational database so you can run SQL queries.

Yeah, you are right about using the OO model, I'll surely be doing that.

The thing is, my project most likely doesn't involve connecting to anything that is online, so no networks, databases and etc. Appreciate the input though.

[jon.kiparsky]

sqlite might be a good contender here.

[Syngenetic]

I decided to stick with what I had earlier. I wasn't importing data from Excel into any data struture, but only display them the way I wanted. But now I ran into a problem and I can't do that.

Suppose if my excel looks something like:

id/ etc / name / price ($)
- - john 5
- - merry 2
- - blake 1
- - john 4
- - john 2

WHat if I want to take average of that, but the name might show up multiple times. How can I go about sorting that or posting the average say, john did average of (5+4+2)?

What type of data structure should I work with? This data will consist of either #, string, or just null value.

I've been thinking a lot and I need to be pointed in the right direction.

[macosxnerd101]

If you have all those attributes, a Person class is the best way to model an individual Person. You could then Map<String, Person>, with the name as the key. Or are these Order objects? Really, a database is the best tool to use here.

[Syngenetic]

macosxnerd101, on 10 July 2012 - 02:30 PM, said:

If you have all those attributes, a Person class is the best way to model an individual Person. You could then Map<String, Person>, with the name as the key. Or are these Order objects? Really, a database is the best tool to use here.

What if there are more attributes that could also play a role? The problem is, I can't work with a different class using the Apache POI unless I am storing the values somewhere. I won't be able to do that.

Map<String,Person> these aren't order objects.

Ok so here is the situation.

I have over 5000+ rows and about 7-8 columns, but I am not necessarly working with all. By using the excel POI, I can easily create a loop and look for certain values and only have them show up. But now my problem is that I have 1 column that holds the price and I need to look at 5000+ rows (actually little smaller after I set if/else condition to show only what I want so less than 100 from those.) and if the same person shows up multiple times, I want to average in with previous years listed in those rows.

If I wasn't working with Excel, I could've used a different approach and etc..but what would be an advantage of using a database or what other way can I go? You might have to explain me in baby steps, sorry and thanks again!

[Syngenetic]

Actually scratch that. I have another way to do this.

Ok macosxnerd101, this is gonna be a little easier to understand.

Month | Name | Price

Nov John $40
Nov Smith $20
Dec John $20
Dec Smith $10
Jan John $15
Jan Smith $12

Except more names and more months(atleast a year)

So lets say name only shows up once per month and I want to get it from a previous month to compare the volume. I think this is more doable approach?

[macosxnerd101]

I'm going to bring back my question from earlier in this thread- what are you modeling? Customer monthly expenditures? You're showing us a lot of data without a lot of clear meaning here.

[Syngenetic]

Yeah, something really close to that. But it will use that similar to this approach:

Month | Name | Price

Nov John $40
Nov Smith $20
Dec John $20
Dec Smith $10
Jan John $15
Jan Smith $12

I'll have few exceptions where I only want to list names of people who have made <=15 dollars
and maybe after sorting the list getting <=15, it would be:

Dec Smith $10
Jan John $15
Jan Smith $12


Ok so, with this information.. maybe I want to now sort it by if the earned value or price average comparing the last 6-12 months is higher or lower than the current month..show them. Then it'll sort that.

I hope this explained it better? Actually it was little more complicated than that, but I kinda worked around the details to get it in this form.

[macosxnerd101]

Let's start with modeling a single row/Object. Design a MonthlyOrder class, with attributes name, month, and amount. Then store a List<MonthlyOrder>. From there, you can sort the List, iterate through it, etc.

[Syngenetic]

Thanks macosxnerd101. That is really a good step towards getting this idea to work. I am always learning new ideas from this forum. Oh and I was reading few articles from your blog..very interesting!

[macosxnerd101]

Glad I could help!

[Syngenetic]

macosxnerd101, I went with that approach..but it was pretty difficult thing to do with excel, or atleast for me. I went back to my original idea...but that seems to be a problem too. I am completely stuck now.

So exact details:

My data contains information from 2011-2012. Now my problem is, apache poi is kinda tough to work with from my part.

Excel data has

Year | Month | Type1 | Type 2 | Type 3 | Customer name 1 | $$

Now these are sorted by years and months. A customer name
can appear multiple times because of different type in the SAME month, or other months during the year.

So What I have done is created a function where it retrieves one month at a type. I only want data from 2012. It will ask the user to enter the month and it will display all the data if the customer made $0 dollars.

I set that data into a arraylist and let it sit there.
Now I want to be able to go back and another column to that arraylist and list the previous month $$ next to it.

Basically, I need to some how compare current month if it meets all the type and customer name to the $$ for past 3-4 months to get the average and have many exceptions.

If $$ = 0, show:

If $$ = 0 for last month and this month, show:

then average and etc.

I am really confused on how to go from what I have already done.

Thanks!!