Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Check the date format. It seems to be DD/MM/YYYY. In that case your expected output is wrong.
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;