When you have different types of data files that are connected through common keys, Pivot Billions allows you to Join these files in a left join fashion. This is very useful if you want to integrate a lookup table to your primary data set. In the following example, we will combine the two sample New York taxi data files and then join the taxi zone lookup table file located here to the combined data set.
To Join data, follow these steps:
- Import the taxi zone lookup file by entering the URL in the data selection box. Click on the Go button to import the file.
- Once the lookup file has loaded, click on the check boxes under the Main heading for the taxi data as shown, and then click on the check box under the Join heading for the taxi zone lookup file you just loaded.
- Select the Preview button to view the column labels and data types. Notice that there is a warning message above the schema preview that indicates there is no matching key column between the Main data set and the Join file. In the Main data set, there is a column labeled PULocationID while in the Join data set there is a column labeled LocationID.
- Change the LocationID label in the Join data set to PULocationID as shown. Notice that the warning message disappears after a matching Key column has been identified.
- Click on the Import button to load both the Main data set and the Join data set. Once it has loaded into the report table, slide right until you see the newly joined data columns of Borough, Zone and service_zone.
- At this point you can now select these columns as dimensions for analysis in pivot tables, as well as perform all other report functions. In this example we demonstrated joining one lookup table file, but you can Join multiple files as necessary following the same steps.