Hello I have two sources in elasticsearch, I wonder if is possible to join them somehow and show one metric from the first source and other metric from the second source in a Trend chart, both sources have a time field.
Trend with Two sources
emoya
#2
Hey sure, it is possible using custom data on a trend chart, let me show you an example:
// get the data
async function getMultipleQueryData() {
return new Promise((resolve, reject) => {
let m1 = cf.Metric("volume", "sum");
let group1 = cf.Attribute("price_date")
.func("MONTH")
.limit(1000)
.sort("asc", "price_date");
let group2 = cf.Attribute("Location")
.limit(1000).func("MONTH")
.sort("asc", "Location");
let metric0 = cf.Metric("OECD - Total", "avg");
var eventsHandler = function(result) {
result.keep = true;
return result;
};
cf
// First query starts
.create("etf") // Any name to identify the query
.provider("Elasticsearch")
.source("etf_prices")
.groupby(group1)
.metrics(m1)
// Second query starts
.create("bc") // Any name to identify the query
.provider("Elasticsearch")
.source("oecd_bussines_confidence")
.groupby(group2)
.metrics(metric0)
.processWith(eventsHandler)
.on("execute:stop", data => {
try {
// Update the visualization if it exists
let akt = cf.getVisualization(widgetId);
if (akt) {
akt
.data(buildData(data))
.execute();
}
resolve(data);
}
catch (e) {
reject(e);
}
})
// This element is a non existing DOM element
// and the purpouse is only to be able to get
// the query using cf.getVisualization('custom-query')
.element("custom-query")
.execute();
});
}
// build the data
function buildData(data) {
const utils = cf.getDependency('utils');
// merging the two datasets (etf and bc) using the utils function
let newData = utils.mergeData(data.data.etf, data.data.bc);
// defining custom data object with data and metadata
const customData = {
groups: [
{
name: "Time",
label: "Time",
func: "MONTH",
type: "TIME"
},
],
metrics: [
{
name: "volume",
label: "ETF volume",
type: "Number",
displayFormat: "$ 0[.]0",
},
{
name: "OECD - Total",
label: "Consumer confidence index",
func: "avg",
hideFunction: true,
type: "Number",
displayFormat: "$ 0[.]0",
position: "right"
}
],
data: newData
};
return customData;
}
// creating the trend visualization
async function createChart() {
let data = await getMultipleQueryData();
let customData = buildData(data);
// Define Grid
let grid = cf.Grid()
.top(40)
.right(56)
.bottom(15)
.left(65);
cf.create()
.graph("Multimetric Trend")
.data(customData)
.set("dataZoom", "dragFilter")
.set("grid", grid)
.set("legend", "top")
.set("yAxis", [{ "show": true }, { "show": true, min: 90, max: 105 }])
.element("chart-container")
.execute();
}
// calling the main function:
createChart();
As you can see, firt we query and build the data, then we use it in a chart, the key here is merging the data with the toolkit util function mergeData.
