Comparative search for multiple fields from 2 sources

[Anonymous]

I would like help in performing the following comparative analysis and count the results.

I have two data sources, from DB1 and DB2. 

Source is Database 1
DB1_User_Name	Ticket_Create_Date	Ticket_no	DB1_Issue_type
Dave	10/11/2018	123	Outlook
Bell	10/11/2018	124	PC
Scott	12/10/2018	125	Application1
Don	21/10/2018	126	Login
Ravi	28/10/2018	127	Outlook
Tim	1/11/2018	128	Application2
Dave	5/11/2018	129	Outlook
Scott	17/11/2018	130	Application3
Bell	14/11/2018	131	Mouse
Don	11/11/2018	132	Application4

Source is Database 2
DB2_User_Name	Search_Date	Search_Session_ID	Action Type	DB2_Issue_type
Mathew	10/10/2018	345	Call	PC
Dave	10/10/2018	456	Search	Outlook
Scott	11/10/2018	567	Search	Application1
Don	20/10/2018	678	Search	Login
Paul	28/10/2018	789	Start	Mouse
Bob	1/11/2018	900	Ping	Application3
Chris	5/11/2018	1011	Call	Application3
Don	17/11/2018	1122	Search	Application4
Tim	14/10/2018	1233	Search	Application2
Don	11/11/2018	1344	Search	Application4

I would like to check if a user listed in DB2 has searched for a particular issue category and later has logged a ticket for the same category in DB1. But I would like to count only tickets logged in DB1 within 3 days from when the search was made in DB2.

So in the above example, Dave has made a search for an Outlook issue on 10/10/2018 and later logged a ticket in DB1 for the same category on 10/11/2018 (This falls within the 3 days bracket). I want to be able to count all such ticket (which are unique) for all such cases.

[jyothish8807]

Hi Don,
May be try like this:

DB1:

Laod

DB1_User_Name & DB1_Issue_type as %Key,

DB1_User_Name,

Ticket_Create_Date,

Ticket_no,

DB1_Issue_type

from DB1;

inner join

DB2:

Load

DB2_User_Name & DB2_Issue_type as %Key

DB2_User_Name,

Search_Date,

Search_Session_ID,

[Action Type],

DB2_Issue_type

from DB2;

Noconcatenate

CheckData:

Load

*,

if(interval(Search_Date - Ticket_Create_Date,'D') <3,1,0) as Flag

resident DB1;

Then At UI, you can use the Flag = 1

Count({<Flag={'1'}>}Ticket_no)

[jyothish8807]

You can drop the table DB1 🙂