topic Comparative search for multiple fields from 2 sources in QlikView

Comparative search for multiple fields from 2 sources

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Comparative search for multiple fields from 2 sources

jyothish8807 — Wed, 21 Nov 2018 06:26:44 GMT

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)

Re: Comparative search for multiple fields from 2 sources

jyothish8807 — Wed, 21 Nov 2018 06:29:41 GMT

You can drop the table DB1 🙂