Join two queries

[tahasefiani]

Hello,

i Have this query that i want to improve

| loadjob savedsearch="myquery"
| where (strftime(_time, "%Y-%m-%d") = "2020-02-27")
|  stats dc(eval(if(STEP="Sent",ID_MESSAGE,NULL))) AS sent,
    dc(eval(if(STEP="RME",ID_MESSAGE,NULL))) AS RME by ID_MESSAGE 
| join type=left ID_MESSAGE [| loadjob savedsearch="myquery" 
| where (strftime(_time, "%Y-%m-%d") >= "2020-02-27") AND (strftime(_time, "%Y-%m-%d") <= "2020-03-03") 
|stats dc(eval(if(STEP="Click",ID_MESSAGE,NULL))) AS click by ID_MESSAGE]
|where sent>0
|stats sum(sent) AS sent,sum(RME) AS RME,sum(click) AS click

This is the result that i have today

And this is the result that i want to have

knowing that RME and click are calculated on the ID_MESSAGE sent

[tahasefiani]

@to4kawa It's not really what i want to do.
There is an explication of what i have today as result and what i want to do

@manjunathmeti

[tahasefiani]

@to4kawa I added a timechart and sum, this is what i have as result

For sent and RME ,i want only ther distinct count of ID_MESSAGE of the 27.
But for click I want distinct count of the ID_MESSAGE(that we have counted on sent) from 27 to 03/03.
The rest is like a countdown of sent

[to4kawa]

I see, the answers is updated.

[to4kawa]

UPDATE2:

| loadjob savedsearch="myquery" 
| where (strftime(_time, "%Y-%m-%d") >= "2020-02-27") AND (strftime(_time, "%Y-%m-%d") <= "2020-03-03") 
| bin span=1d _time
| stats dc(eval(STEP="Sent" AND strftime(_time,"%F")="2020-02-27")) AS sent
, dc(eval(STEP="RME" AND strftime(_time,"%F")="2020-02-27")) AS RME
, dc(eval(STEP="Click")) AS click by _time ID_MESSAGE 
| search ID_MESSAGE="ABC" OR ID_MESSAGE="XYZ"
| fields - ID_MESSAGE
| stats sum(*) as * by _time
| eval The_rest=sent + RME - click
| streamstats sum(The_rest) as "The rest"
| table _time  sent RME click "The rest"

I'm not sure the logs and recently query still
now.

[manjunathmeti]

Hi @tahasefiani,
Try this.

| loadjob savedsearch="myquery" 
| where (strftime(_time, "%Y-%m-%d") >= "2020-02-27") AND (strftime(_time, "%Y-%m-%d") <= "2020-03-03") 
| stats dc(eval(if(STEP="Click",ID_MESSAGE,NULL))) AS click by ID_MESSAGE 
| timechart span=1d sum(click) AS click 
| eval sent= 
    [| loadjob savedsearch="myquery" 
    | where (strftime(_time, "%Y-%m-%d") = "2020-02-27") 
    | stats dc(eval(if(STEP="Sent",ID_MESSAGE,NULL))) AS sent by ID_MESSAGE 
    | stats sum(sent) AS sent 
    | return $sent] 
| eval RME= 
    [| loadjob savedsearch="myquery" 
    | where (strftime(_time, "%Y-%m-%d") = "2020-02-27") 
    | stats dc(eval(if(STEP="RME",ID_MESSAGE,NULL))) AS RME by ID_MESSAGE 
    | stats sum(RME) AS RME 
    | return $RME] 
| eval the_rest=sent-(RME+click)

[tahasefiani]

Thanks @manjunathmeti
But there is a problem in your query! The result which I want on "click" and "RME", it's always on ID_MESSAGE recover in "sent" of 27/02/2020. And besides that is why I had used the join in my query.

Example: Admitting that we have "XYZ" and "WVT" as ID_MESSAGE on "sent"
The search in "click" and "RME" must be on "XYZ", "WVT"

[manjunathmeti]

try this:

| loadjob savedsearch="myquery" 
| where (strftime(_time, "%Y-%m-%d") >= "2020-02-27") AND (strftime(_time, "%Y-%m-%d") <= "2020-03-03") 
| stats dc(eval(if(STEP="Click",ID_MESSAGE,NULL))) AS click by ID_MESSAGE 
| timechart span=1d sum(click) AS click by ID_MESSAGE
| join type=left ID_MESSAGE 
     [| loadjob savedsearch="myquery" 
     | where (strftime(_time, "%Y-%m-%d") = "2020-02-27") 
     | stats dc(eval(if(STEP="Sent",ID_MESSAGE,NULL))) AS sent, dc(eval(if(STEP="RME",ID_MESSAGE,NULL))) AS RME by ID_MESSAGE 
     | stats sum(sent) AS sent, sum(RME) AS RME by ID_MESSAGE]
| eval the_rest=sent-(RME+click)