Want to know the how many times did the Caller reached for same issue within 24 hours

[khajafareed]

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

[villegasi03]

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. 

[villegasi03]

sorry forgot the part about the same day. 

[villegasi03]

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) ;

[khajafareed]

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]) ;