Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have data in below format, my requirement is - i want to know if the caller reached for same Category and Sub Category issue within 24 hours of time and also how many times Caller reached to ITSD for same issue.
Please advise how can we get above requirement.
Number | Caller | Category | Sub Category | Created | Helpdesk Type |
IN0114680 | Justin Bell | Physical Device | Break/Fix | 2020-10-23 02:07:29 | IT Service Desk |
IN0114685 | Robins | Application/Orders/Results | Results | 2020-10-23 01:26:19 | IT Service Desk |
IN0114686 | Ashi Moh | Access | Password Reset | 2020-10-23 00:02:01 | IT Service Desk |
IN0114687 | Lucy Samuels | Access | Account Unlock | 2020-10-23 00:35:45 | IT Service Desk |
IN0114689 | George Mille | Data | Data Loss | 2020-10-23 00:27:15 | IT Service Desk |
There might be an easier way but here is what comes to mind.
Load
Caller,
(Category & ' - ' & Sub Category) as Cat_SubCat,
Count(Number) as NumOfCalls
From InputData Group by Caller, (Category & ' - ' & Sub Category) ;
This should give you a count the number of calls by category & sub category.
sorry forgot the part about the same day.
Caller,
Date(Floor(Created), 'mm/dd/yyyy') as CreatedDt,
(Category & ' - ' & Sub Category) as Cat_SubCat,
Count(Number) as NumOfCalls
From InputData Group by Caller, Date(Floor(Created), 'mm/dd/yyyy'), (Category & ' - ' & Sub Category) ;
Thanks for Response, i tried but got invalid expression error. Also i would like to know the caller details as well.
LOAD Number,
Count(Number) as NumOfCalls,
Caller,
Category,
[Sub Category],
(Category & ' - ' & [Sub Category]) as Cat_SubCat,
Created,
Date(Floor(Created), 'mm/dd/yyyy') as CreatedDt,
[Helpdesk Type]
FROM [C:\Users\Dev\Downloads\interaction.xlsx] (ooxml, embedded labels, table is [Page 1])
Group by Caller, Date(Floor(Created), 'mm/dd/yyyy'), (Category & ' - ' & [Sub Category]) ;