Is it possible to combine two datatable from multiple essql filters

[tangkalo]

Hi, I have a data source like below: -

TYPE VALUE
A 123
A 234
B 890
A 789
B 085

I need to pull the latest record for each type i.e. there will be two rows in the result : -

A 123
B 890

I think I cannot do it with elasticsearch sql(please correct me if I am wrong). If not, can I have two essql filters in my expression and somehow combine them? The two essql filters will be : -

SELECT TYPE, VALUE FROM SOURCE WHERE TYPE = A ORDER BY @timestamp DESC LIMIT 1

SELECT TYPE, VALUE FROM SOURCE WHERE TYPE = B ORDER BY @timestamp DESC LIMIT 1

Is it possible?

Thanks a lot in advance.

[dosant (Anton Dosov)]

Hi @tangkalo, since you've asked this question in #elastic-stack:kibana section, I assume you are using canvas and this is a question about visualizing your data?

I don't think it is possible to combine two data tables into a single table this way.
As a presentation alternative, I'd suggest to create two separate canvas metric charts and configure each two display last values for its own type.

[tangkalo]

@dosant

canvas metric charts

Thanks for the suggestion. Yes, it's in canvas. By metric charts, do you mean the Metric element?

On the other hand, I am also thinking of manually creating a table and populating it with customized values. I have been looking at Markdown element but haven't been able to find the syntax that allows me to create a table.

[dosant (Anton Dosov)]

By metric charts, do you mean the Metric element?

Yes!