Pages: [1] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Simon Hashur
Imperial Academy Amarr Empire
0
|
Posted - 2017.05.13 12:20:03 -
[1] - Quote
Hi folks,
I'm looking for a list of BPO's that require only minerals to produce and a way to get those into a useable spreadsheet.
i've been banging away at this on and off for the last 2-3 weeks (5hrs straight today), searched high and low for what i want and how to do it.
i'm trying to do it in excel as i'm finding the g.docs hamsters go on strike for some of the sheets (Fuzz's BPmats QUERY sheet for example (which i can't get to work in excel))
i've downloaded all relevant files from Fuzzworks, looked up god know's how many google sheets, made/manipulated a boat load of pivot tables, tediously gone trhough and deleted rows/columns containing stuff that's not relevant to what i'm trying to do but i'm still no closer to what i want
i don't have much of a clue when it comes to excel/g.docs so any info/advice anyone has, could you ELI5
thanks
|
Blacksmoke16
Resilience.
59
|
Posted - 2017.05.13 13:11:23 -
[2] - Quote
So all you want is the names of the bpos? No other information? |
Steve Ronuken
Fuzzwork Enterprises Vote Steve Ronuken for CSM
6712
|
Posted - 2017.05.13 17:01:28 -
[3] - Quote
As blacksmoke shows, it's _far_ easier to do this in a a database, than in a spreadsheet.
I highly recommend getting a copy (from my site) and using an odbc connection with excel to query it. Either mysql or sqlite (postgres is best, but more painful to work with if you don't know what you're doing)
Woo! CSM XI!
Fuzzwork Enterprises
Twitter: @fuzzysteve on Twitter
|
Simon Hashur
Imperial Academy Amarr Empire
0
|
Posted - 2017.05.20 14:18:25 -
[4] - Quote
thanks for the replies, i appreciate it.
also, sorry for the late reply.
@Blacksmoke
yes mate, i was after the minerals for them too. i forgot to specify in my original post (rush rush rush!! lol!!)
the list you provided was still very helpful though so thankyou for that i managed to filter out the vast majority of what i didn't need from it. after that, i went through and removed faction/special edition items.
pivot table and filtering got me the mineral requirements for them too.
all i need to do now is figure out an easy way to pull all the market info i require for them into a spread.
@Steve
thanks for the advice. i think databases and SQL stuff is a bit beyond my scope atm but it is something i'm interested in learning though.
do you have a link to the one you mentioned on your site? i have had a look around but can't see it.
|
Blacksmoke16
Resilience.
64
|
Posted - 2017.05.20 16:21:04 -
[5] - Quote
With each mineral/quantity:
SELECT bpI.typeName, bpI.typeID, matI.typeName, bp.quantity FROM industryactivitymaterials bp JOIN invTypes bpI ON bp.typeID = bpI.typeID JOIN invTypes matI ON bp.materialTypeID = matI.typeID WHERE bp.activityID = 1 and bpI.published = true GROUP BY bpI.typeName, matI.typeName HAVING GROUP_CONCAT(DISTINCT matI.groupID) = '18'
https://drive.google.com/open?id=0B0aKNcciVJ-UTEU3Z0trOEpqNE0
I included the typeID for the item as well. You can use https://github.com/nuadi/googlecrestscript to get market prices given IDs if using Google sheets.
|
guigui lechat
the no fock given
39
|
Posted - 2017.05.21 15:09:03 -
[6] - Quote
what he wants I guess is a spreadsheet containing all the material requirement of the BPos so to know the build cost of each. |
Simon Hashur
Imperial Academy Amarr Empire
0
|
Posted - 2017.05.24 21:54:12 -
[7] - Quote
Quote:guigui lechat Posted: 2017.05.21 15:09
what he wants I guess is a spreadsheet containing all the material requirement of the BPos so to know the build cost of each.
yes, that is what i'm aiming to do.
i'd like to try and do/learn as much as i can without being spoon fed everything or being given a completed project. i'll have a better understanding of it then.
asking for help/pointers is something i'll do when i really get stuck on something.
Quote:Blacksmoke16 Posted: 2017.05.20 16:21 With each mineral/quantity: SELECT bpI.typeName, bpI.typeID, matI.typeName, bp.quantity FROM industryactivitymaterials bp JOIN invTypes bpI ON bp.typeID = bpI.typeID JOIN invTypes matI ON bp.materialTypeID = matI.typeID WHERE bp.activityID = 1 and bpI.published = true GROUP BY bpI.typeName, matI.typeName HAVING GROUP_CONCAT(DISTINCT matI.groupID) = '18' https://drive.google.com/open?id=0B0aKNcciVJ-UTEU3Z0trOEpqNE0 I included the typeID for the item as well. You can use https://github.com/nuadi/googlecrestscript to get market prices given IDs if using Google sheets. '
again, thanks for the info, i appreciate it :)
i did manage to get to there with excel but your query/statement will deffo come in useful if i move on to SQL.
i was a bit dissappointed to see that the doc. would only pull 10-20 items at a time though. i'd like to be able to pull a lot more than that tbh.
|
|
|
|
Pages: [1] :: one page |
First page | Previous page | Next page | Last page |