Data Collection And Use Discussion

[qesehmk]

I have never understood Spec how you do this! I don't believe you actually manually do this. Let it be your secret. You don't have to tell us :)
Or you can look here (http://www.qesehmk.org/forums/showthread.php/2404-Trackitt-Comparison-FY2015-vs-FY2014-vs-FY2013-vs-FY2012-vs-FY2011) for the figures based on those who have updated their Trackitt profile.

[Spectator]

I have never understood Spec how you do this! I don't believe you actually manually do this. Let it be your secret. You don't have to tell us :)Q,

It's not as bad as you would think.

I'm reasonably handy with Excel, so that is the basis for it. I'm sure there are better ways, but my skills are not sufficient.

Originally, I did do it manually, but it has evolved over the years because that was simply too time consuming.

Now the only manual steps are to copy/paste the data from Trackitt into Excel and copy/paste the output from Excel into the forum.

The data from Trackitt goes into an Excel table and then that automatically populates various analyses in another sheet. In turn, the relevant figures are automatically converted to the correct output for the forum, including forum specific formatting, in another sheet. It's then a simple copy/paste into the forum. Of course, it did take a while to set up originally.

At one point, it was possible to do a web query from Excel on the Trackitt tracker, but Trackitt made changes last year which have broken that functionality.

If my VBA skills were better (they are virtually non existent), I could probably automate it further within Excel.

TBH, the most time consuming step is error checking the Trackitt data, which isn't always of the highest quality for pending cases in particular.

It's the same with the PERM data. I originally did that all in Excel, but the amount of data became too large and took to long to run. As a solution I did a quick "teach yourself Access" (the rather ancient Access 2003 I have) and a bit of SQL sufficient to move the data to that and make the relationships between the tables and create the queries. So now, the analysis is still done by pivot table in Excel using data pulled in from Access. That's grown to in excess of half a million records now. It's a little more time consuming to generate the actual forum output, but it's only once every quarter.

I get the DOL LCR data from dolstats/permchecker, which is analysed via pivot table. Clearly they have a means to extract the data directly from the LCR database.

I realize I have probably given the IT folks on the forum a good laugh, but I do the best I can with the limited knowledge I have. I like problem solving and making things simpler and quicker. I find having an actual problem to solve is the best way to learn and to retain the knowledge. It may not be the most efficient though.

I do wonder sometimes how many people actually look at the posts. I know there are a few regulars. It doesn't really matter, since I collect and analyze the data anyway and I am happy to share it.

[imdeng]

Come on! You are underselling yourself. Calling trackitt from Excel, writing SQL queries, learning all this so that you can contribute better to the community...

BTW - if you ever need any help with VBA/SQL/Whatever - you just need to ask and I am sure there would be enough people around who would love to help you.


I realize I have probably given the IT folks on the forum a good laugh, but I do the best I can with the limited knowledge I have. I like problem solving and making things simpler and quicker. I find having an actual problem to solve is the best way to learn and to retain the knowledge. It may not be the most efficient though.

[greenercard]

Spec you might want to give "powerpivot" a shot. It comes free with Excel I believe 2010 and forward (you need to download the plugin). You can crunch millions of records and create data models visually.

[Spectator]

Spec you might want to give "powerpivot" a shot. It comes free with Excel I believe 2010 and forward (you need to download the plugin). You can crunch millions of records and create data models visually.greenercard,

Thanks for the suggestion.

[suninphx]

Q,

It's not as bad as you would think.

I'm reasonably handy with Excel, so that is the basis for it. I'm sure there are better ways, but my skills are not sufficient.

Originally, I did do it manually, but it has evolved over the years because that was simply too time consuming.

Now the only manual steps are to copy/paste the data from Trackitt into Excel and copy/paste the output from Excel into the forum.

The data from Trackitt goes into an Excel table and then that automatically populates various analyses in another sheet. In turn, the relevant figures are automatically converted to the correct output for the forum, including forum specific formatting, in another sheet. It's then a simple copy/paste into the forum. Of course, it did take a while to set up originally.

At one point, it was possible to do a web query from Excel on the Trackitt tracker, but Trackitt made changes last year which have broken that functionality.

If my VBA skills were better (they are virtually non existent), I could probably automate it further within Excel.

TBH, the most time consuming step is error checking the Trackitt data, which isn't always of the highest quality for pending cases in particular.

It's the same with the PERM data. I originally did that all in Excel, but the amount of data became too large and took to long to run. As a solution I did a quick "teach yourself Access" (the rather ancient Access 2003 I have) and a bit of SQL sufficient to move the data to that and make the relationships between the tables and create the queries. So now, the analysis is still done by pivot table in Excel using data pulled in from Access. That's grown to in excess of half a million records now. It's a little more time consuming to generate the actual forum output, but it's only once every quarter.

I get the DOL LCR data from dolstats/permchecker, which is analysed via pivot table. Clearly they have a means to extract the data directly from the LCR database.

I realize I have probably given the IT folks on the forum a good laugh, but I do the best I can with the limited knowledge I have. I like problem solving and making things simpler and quicker. I find having an actual problem to solve is the best way to learn and to retain the knowledge. It may not be the most efficient though.

I do wonder sometimes how many people actually look at the posts. I know there are a few regulars. It doesn't really matter, since I collect and analyze the data anyway and I am happy to share it.

Spec, one word - Outstanding!!

[qesehmk]

Spec - despite of what you say - it's a lot of effort and commitment. I don't think a lot of people on the forum realize it. I know a lot do. But perhaps not a lot more probably don't. Thanks anyway!

Q,

It's not as bad as you would think.

I'm reasonably handy with Excel, so that is the basis for it. I'm sure there are better ways, but my skills are not sufficient.

Originally, I did do it manually, but it has evolved over the years because that was simply too time consuming.

Now the only manual steps are to copy/paste the data from Trackitt into Excel and copy/paste the output from Excel into the forum.

The data from Trackitt goes into an Excel table and then that automatically populates various analyses in another sheet. In turn, the relevant figures are automatically converted to the correct output for the forum, including forum specific formatting, in another sheet. It's then a simple copy/paste into the forum. Of course, it did take a while to set up originally.

At one point, it was possible to do a web query from Excel on the Trackitt tracker, but Trackitt made changes last year which have broken that functionality.

If my VBA skills were better (they are virtually non existent), I could probably automate it further within Excel.

TBH, the most time consuming step is error checking the Trackitt data, which isn't always of the highest quality for pending cases in particular.

It's the same with the PERM data. I originally did that all in Excel, but the amount of data became too large and took to long to run. As a solution I did a quick "teach yourself Access" (the rather ancient Access 2003 I have) and a bit of SQL sufficient to move the data to that and make the relationships between the tables and create the queries. So now, the analysis is still done by pivot table in Excel using data pulled in from Access. That's grown to in excess of half a million records now. It's a little more time consuming to generate the actual forum output, but it's only once every quarter.

I get the DOL LCR data from dolstats/permchecker, which is analysed via pivot table. Clearly they have a means to extract the data directly from the LCR database.

I realize I have probably given the IT folks on the forum a good laugh, but I do the best I can with the limited knowledge I have. I like problem solving and making things simpler and quicker. I find having an actual problem to solve is the best way to learn and to retain the knowledge. It may not be the most efficient though.

I do wonder sometimes how many people actually look at the posts. I know there are a few regulars. It doesn't really matter, since I collect and analyze the data anyway and I am happy to share it.

[sportsfan33]

Q,

It's not as bad as you would think.

I'm reasonably handy with Excel, so that is the basis for it. I'm sure there are better ways, but my skills are not sufficient.

Originally, I did do it manually, but it has evolved over the years because that was simply too time consuming.

Now the only manual steps are to copy/paste the data from Trackitt into Excel and copy/paste the output from Excel into the forum.

The data from Trackitt goes into an Excel table and then that automatically populates various analyses in another sheet. In turn, the relevant figures are automatically converted to the correct output for the forum, including forum specific formatting, in another sheet. It's then a simple copy/paste into the forum. Of course, it did take a while to set up originally.

At one point, it was possible to do a web query from Excel on the Trackitt tracker, but Trackitt made changes last year which have broken that functionality.

If my VBA skills were better (they are virtually non existent), I could probably automate it further within Excel.

TBH, the most time consuming step is error checking the Trackitt data, which isn't always of the highest quality for pending cases in particular.

It's the same with the PERM data. I originally did that all in Excel, but the amount of data became too large and took to long to run. As a solution I did a quick "teach yourself Access" (the rather ancient Access 2003 I have) and a bit of SQL sufficient to move the data to that and make the relationships between the tables and create the queries. So now, the analysis is still done by pivot table in Excel using data pulled in from Access. That's grown to in excess of half a million records now. It's a little more time consuming to generate the actual forum output, but it's only once every quarter.

I get the DOL LCR data from dolstats/permchecker, which is analysed via pivot table. Clearly they have a means to extract the data directly from the LCR database.

I realize I have probably given the IT folks on the forum a good laugh, but I do the best I can with the limited knowledge I have. I like problem solving and making things simpler and quicker. I find having an actual problem to solve is the best way to learn and to retain the knowledge. It may not be the most efficient though.

I do wonder sometimes how many people actually look at the posts. I know there are a few regulars. It doesn't really matter, since I collect and analyze the data anyway and I am happy to share it.

Spec,

Outstanding post. Contrary to what you believe, your originality and problem solving ability will put 99% of the IT code monkeys to shame. I am one of the code monkeys myself...not afraid to admit it. I doubt if I can show the same original thought and implementation as you have demonstrated.

I will communicate with you on the PM over the next few days to get started on my own data mining project. It is time to give back to the community.

[Spectator]

Thanks for the kind words everybody.

TBH, it felt like blowing my own trumpet, which is not my style or something I'm comfortable doing.

I'm glad some people found the post interesting.