Documentation

Cross Query On The Fly

After you have joined your data sources in the data connection screen you are ready to cross query on the fly. Creating a chart from different data sets works just like creating a chart from one single data source in datapine. You just need to make sure that all data sources are located in our data warehouse and that you’ve joined the data sources in the settings section using foreign keys before you start creating a chart.

 

  1. Open the Chart creator with a click on Analyze on the navigation bar.
  2. You can now select the first data source in the dropdown at the top of the left data source schema and then drag and drop any field from the data schema into Measures and Dimensions on the right.
  3. To add a field from another source, just select the data source on top of the data schema and then add a field to the right.

Sometimes you might need to change the Join Type when cross-querying to get the right results. Please click here to learn how to change the join type.

 

Cross Query with SQL codes

 

If you wish to run SQL Codes to cross query your data in datapine, the addressing fields from different data sources need to be identified using curly brackets in front of the table names.

 

In the following example, we will join a table from a sales database with a spreadsheet containing the exchange rates for foreign currencies to calculate our revenue per product in our domestic currency. As you can see, we’ve added the data sources names in front of the table names once and then added aliases.

 

SELECT a.`Product` as ‘Product’, a.`Currency` as ‘Currency’, sum(a.`Revenue`)*b.`Rate` as ‘Revenue’

FROM {SalesDB}`Sales` a

JOIN {Currencies}`ExchangeRates` b

ON a.`Currency = b.`Currency`

GROUP BY a.`Product`, a.`Currency`