Is it possible to compare one Query data with anothers?

Elastic Stack Kibana
,
1

Hi im trying to compare two table cells. I manage to get two queries work fine, but now i need how can i implement first queries table value into anothers and then get the result. Here is an example:
I have one table:

userID 	time 
2	2020-04-15T03:00:00+03:00
3	2020-04-13T03:00:00+03:00
3	2020-04-14T03:00:00+03:00
3	2020-04-15T03:00:00+03:00
3	2020-04-16T03:00:00+03:00
3	2020-04-17T03:00:00+03:00
3	2020-04-18T03:00:00+03:00
3	2020-04-19T03:00:00+03:00
3	2020-04-20T03:00:00+03:00
663	2020-04-21T03:00:00+03:00

and in the second query I have specifics date unique user IDs:

ProductLicenseKey 	Time 
3	2020-04-16T23:05:00+03:00

Now what i want to do is to create a new Collum named "Retained User". And I need to check whether Table1 ProductLicenseKey is in Table2 ProductLicenseKey.
So I tied like this:

filters
| essql 
  query="Select \"ProductLicenseKey\" as userID,
HISTOGRAM(\"Time\", INTERVAL 1 Day) as time From \"logsystem.logs\"
Where \"ActionName\" like 'License Activation'
Group By ProductLicenseKey,time"
| mapColumn "retainedUser"
  fn={filters | essql query="SELECT COUNT(\"ProductLicenseKey\") as Count FROM \"logsystem.logs\" Where \"Time\" >= '2020-04-16T00:00:00' AND \"Time\" <= '2020-04-16T23:59:59' AND \"ActionName\" like 'License Activation' AND \"ProductLicenseKey\" = userID" | math "first(Count)"}
| table
| render

But getting the error that unknown column 'UserID". Any one can suggest any approach for this situation? Tried to do with Switch statement. but got no luck either.

2

Hi @Julius_Kukonenko

This is a tricky issue because you lose the context of the first query when you you enter the mapColumn subexpression and do filters

Is there anyway to get all the information you need from just a single query?

We do have some enhancements coming in a future version of Canvas that will allow you to hold onto the result of the query and use it somewhere later in the expression, but no guarantees when it's going to land and be released.

3

Is this an expression from a Canvas workpad you are creating?

4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.