topic Re: Comparative search for multiple fields from 2 sources in QlikView

Comparative search for multiple fields from 2 sources

Anonymous — Sat, 16 Nov 2024 21:51:38 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

Saravanan_Desingh — Wed, 29 Apr 2020 01:01:34 GMT

Check the date format. It seems to be DD/MM/YYYY. In that case your expected output is wrong.

Re: Comparative search for multiple fields from 2 sources

Saravanan_Desingh — Wed, 29 Apr 2020 01:07:46 GMT

One solution is:

SET DateFormat='D/M/YYYY'; tab1: LOAD * INLINE [ 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 ]; Join tab2: LOAD *, DB2_User_Name As DB1_User_Name, DB2_Issue_type As DB1_Issue_type ; LOAD * INLINE [ 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 ]; tab3: LOAD *, If(Ticket_Create_Date >= Search_Date And Ticket_Create_Date <= Search_Date+3,'Y') As Flag Resident tab1; Drop Table tab1;