# Is it possible to compare one Query data with anothers?

**URL:** <https://discuss.elastic.co/t/is-it-possible-to-compare-one-query-data-with-anothers/229046>\
**Category:** Kibana\
**Tags:** elastic-stack-sql, canvas\
**Created:** [April 21, 2020, 1:11pm UTC](https://discuss.elastic.co/t/is-it-possible-to-compare-one-query-data-with-anothers/229046 "2020-04-21T13:11:22Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Julius\_Kukonenko](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/julius_kukonenko/32/63653_2.png) [@Julius\_Kukonenko](https://discuss.elastic.co/u/Julius_Kukonenko)\
**Post date:** [April 21, 2020, 1:11pm UTC](https://discuss.elastic.co/t/is-it-possible-to-compare-one-query-data-with-anothers/229046/1 "2020-04-21T13:11:22Z")

</div>

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:

```auto
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:

```auto
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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![corey.robertson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/corey.robertson/32/54611_2.png) [@corey.robertson](https://discuss.elastic.co/u/corey.robertson)\
**Post date:** [May 12, 2020, 2:00pm UTC](https://discuss.elastic.co/t/is-it-possible-to-compare-one-query-data-with-anothers/229046/2 "2020-05-12T14:00:27Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![thomasneirynck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/thomasneirynck/32/23313_2.png) [@thomasneirynck](https://discuss.elastic.co/u/thomasneirynck)\
**Post date:** [May 12, 2020, 2:01pm UTC](https://discuss.elastic.co/t/is-it-possible-to-compare-one-query-data-with-anothers/229046/3 "2020-05-12T14:01:22Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 9, 2020, 2:01pm UTC](https://discuss.elastic.co/t/is-it-possible-to-compare-one-query-data-with-anothers/229046/4 "2020-06-09T14:01:23Z")

</div>

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