Mobile Jobs by hire company
The first thing to do is open Studio 3T and access the mongo database by clicking on the green connect button and then clicking connect.
Mobile Jobs by hire company Mobile Jobs by hire company
On the left hand side, under mobile click on view and then double-click on mobileJobsByHireCompany.
It may take a while for this to display the results so move on to the next report that needs running for now and we will come back to this.
open GDEX and go to the Remote Queries tab at the top.
Highlight all the companies by clicking on CTRL + A.
Then on the right in the query box click on the drop-down box and scroll to the top and click on the Booking Countv2.
When the report has been selected click on Execute query.
You can switch to the 'Status messages' tab to see how far the query has got.
Once the query has finished (click the Output tab if you moved off it) and click 'Export to Excel to export the data to excel.
Save the file name as Booking Countv3_<date>.csv e.g. Booking Countv3.06/12/2022
You might need to make some adjustments to make the spreadsheet look better, but once you have finished save the file somewhere on your PC. Might be best to create a new folder to store them in. You can close the file now.
The mongo query should now have run and will show the first 50 records. To show all the records adjust the figure from 50 to 10000, this will show all the records.
Then click on the export button at the top of the screen.
On the first page, select the second option “Current Query result”.
Select the second option “CSV”.
Click Next until you reach the below screen.
Where you will be able to browse to the destination folder to where you want to save the export.
The file name needs to be saved as mobile.mobileJobsByHireCompany_<date>.csv e.g. mobile.mobileJobsByHireCompany_06-12-2022.csv
Then click next and start exporting. This can take a few minutes so it is best to leave it now and check back in about 5 minutes. You will know when it has been completed successfully as the bottom right of your screen should look like this.
You can close mongo now if you want to.
Fleet Count with company Hire ID
In Gdex you now need to run another report called 'Fleet Count with Company Hire ID', this report gets all the companies and their company IDs together along with the fleet size. This report is used for the report that’s being run in mongo because mogo doesn’t have the fleet sizes.
Select the report from the drop-down and click 'Execute Query'.
Once the query has run export it to excel like before but you don’t need to save it as this will only be used for a vlookup.
You can now close gdex if you want to.
Next, Open up the exported mongo report and create a new column at the end called fleet, this is where the vlookup will go to get the fleet sizes.
Perform the vlookup using the company id as the link and do it for all the cells.
Then save the exported mogo file.
See below if you need to know how to perform the vlookup.
How to do a vlookup
1. Have both spreadsheets open, the mongo exported data and the gdex exported data from 'Fleet Count with Company Hire ID'
2. In the mongo exported excel document, in the fleet column (you created) type =vlookup(
2. Then click on the “A2” cell as this is what we will be using to search for the fleet size
3. add a comma4. In the 'Fleet Count with Company Hire ID' sheet select all of the cells within the HIRE_ID and FLEET_SIZE column like below.
Make sure you are selecting all the records.
4. Back in the mongo export now add another comma and then enter the column number you would like to return.
In our case, it will be “2” as this is the FLEET_SIZE column in the 'Fleet Count with Company Hire ID' spreadsheet.
5. Add another comma and type the word “False”, this will make sure that all records are an exact match.
5. Put a closing bracket ) at the end and hit enter. It should look like the below.
6. Double-click the little green box on the bottom right of the vlookup cell to expand the results.
and please send copies to Paul N on Teams.