Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
donbosco_l
Contributor II
Contributor II

Comparative search for multiple fields from 2 sources

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_NameTicket_Create_DateTicket_noDB1_Issue_type
Dave10/11/2018123Outlook
Bell10/11/2018124PC
Scott12/10/2018125Application1
Don21/10/2018126Login
Ravi28/10/2018127Outlook
Tim1/11/2018128Application2
Dave5/11/2018129Outlook
Scott17/11/2018130Application3
Bell14/11/2018131Mouse
Don11/11/2018132Application4

 

Source is Database 2    
     
DB2_User_NameSearch_DateSearch_Session_IDAction TypeDB2_Issue_type
Mathew10/10/2018345CallPC
Dave10/10/2018456SearchOutlook
Scott11/10/2018567SearchApplication1
Don20/10/2018678SearchLogin
Paul28/10/2018789StartMouse
Bob1/11/2018900PingApplication3
Chris5/11/20181011CallApplication3
Don17/11/20181122SearchApplication4
Tim14/10/20181233SearchApplication2
Don11/11/20181344SearchApplication4

 

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.

2 Replies
jyothish8807
Master II
Master II

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)

 

Best Regards,
KC
jyothish8807
Master II
Master II

You can drop the table DB1 🙂
Best Regards,
KC