Count the number of tickets in last X days

Help Studio
1

Hi all,
I am reading a CSV file which is basically an Incident dump, and also using an Input Dialog i should collect No. of days and ultimately show how many number of tickets were raised for these many number of days.
I have used a Read CSV file, Input Dialog, SOrt datatable(based on the column that has the ticket raised date, Multiple assign workflow where I have to manually assign the latest date of the Incident dump file(the dump is of last year so), firstly how do I do this and secondly , how do I count the number of tickets raised for so many number of days?

2

@niranjan_shetty

Welcome to the community

So do you need like each ticket how many days each invoice is waiting from?

Or need the total count of tickts and the time for all of them together in days that is the difference between first or minimum raise date and today or the mac completed date if all completed

Cheers

3

Hi @Anil_G
Thanks and much happy to be here.
Since, this is an ServiceNow Incident dump, there is no question of Invoices.
All I need is total number of tickets raised for the x Days, time is not required, just the count of number of tickets raised/created is required

4

How does your data table look like? (columns and data types)
How many rows are there?

In the input dialog where you try to collect no. of days; is that an integer? Like 5? What is the point of it? Would it then be like in the last 5 days? Or is it a date value?

5

Also, I used an Multiple Assign acivity and did as seen below:
dtCsv_excel_newest_date = DateTime.ParseExact(“31/08/2016”, “dd/MM/yyyy”, Nothing)
dtCsv_excel_xthdate = dtCsv_excel_newest_date.AddDays(-x)

Now, dtCsv_excel_xthdate shows the correct date, i just need to get the count of tickets between dtCsv_excel_newest_date & dtCsv_excel_xthdate

6

The data table is default data types i guess as I used Read CSV activity
There are 1.41lakh rows
Yes x in Input dialog is an Int32 datatype

7

Ok, this clarifies. So the integer is used for the AddDays functionality.

For this you have the 2 variables now. With ‘Filter Datatable’ activity you can filter the data table and store the output as a new datatable. You can use 2 conditions

image
image744×479 17.9 KB

Then following that, you can do DataTable.Rows.Count on the new data table. That will give you back the numer of rows.

Edit: 1.41 lakh = 141.000
Quite a bit. You might hit performance issue, considering UiPath will load the data table in memory. Depends a bit how complex the csv is.

8

Thanks, yes i was working with filter datatable and using the Configure filter,i did below:
“opened_at” <= dtCsv_excel_newest_date
“opened_at” >= dtCsv_excel_xthdate

But ,the filter is not working at all

9

Hi @niranjan_shetty ,

I believe the Column Data types are String and hence maybe the Filtering is not happening properly.

To Be more precise on providing a suitable suggestions, could you maybe provide us with a Sample Data that you have, so that we can understand the date formats and the way it needs to be converted.

10

31-08-2016 15:17
31-05-2016 20:55
31-05-2016 20:53
31-05-2016 20:43
31-05-2016 20:15
31-05-2016 20:15
31-05-2016 20:15
31-05-2016 19:34
31-05-2016 19:34
31-05-2016 19:34
31-05-2016 19:26
31-05-2016 19:26
31-05-2016 19:25
31-05-2016 19:25
31-05-2016 19:25
31-05-2016 19:22

11

I am not sure whether this is correct way of thinking, i see that in the Data table the value is 31-05-2016 19:22 and when i use Adddays function i see the data is in 14/06/2016 00:00 format, is it due to this “/” “-” difference the filter is not working ??

12

Hi @niranjan_shetty

  1. Read CSV
  2. Input Dialog (store in NumberOfDays)
  3. Sort DataTable (based on ticket raised date)
  4. Assign (LatestDate = YourDataTable.Rows(0)(“YourDateColumnName”).ToString)
  5. Assign (StartDate = LatestDate.AddDays(-NumberOfDays))
  6. Filter DataTable (Condition: “YourDateColumnName >= #” & StartDate.ToString(“yyyy-MM-dd”) & “#”)
  7. Assign (TicketCount = FilteredDataTable.Rows.Count)

Hope this helps

13

@niranjan_shetty

Please try this in assign

Requireddays = Dt.AsEnumerable.Where(function(x) CDate(x("Start/createddatecol").ToString)>Now.addays(-NumberOfdays)).Count

Cheers

16

What should be the datatype of LatestDate here, i am using the System.Datetime and so it is giving me assignment error, also I am using multiple assign instead of Assign

17

@Anil_G
I have not tried this as this will not fit my scenario as the dump consists of Incident state field and I will have to use the Filter to select only New incidents

18

@niranjan_shetty

You can add that filter as well here

Requireddays = Dt.AsEnumerable.Where(function(x) x("State").ToString.Equals("RequiredState") AndAlso CDate(x("Start/createddatecol").ToString)>Now.addays(-NumberOfdays)).Count

Cheers

19

Hi @niranjan_shetty

Can you share the xaml file or screenshots

20

image
image1224×1384 48.1 KB

For some reason i am unable to upload the file, it says i am a new user

21

@Anil_G
I did change the above cmdlet a little biit
dtCsv.AsEnumerable.Where(Function(r) r(“incident_state”).ToString.Equals(“New”) AndAlso CDate(r(“opened_at”).ToString)>CDate(Latestdate).adddays(-x)).Count

Because I had a predefined date in my case, i used a Assign and assigned this to LatestDate
dtcsv.Rows(0)(dtCsv.Columns(10)).ToString
LatestDate is a string

And now I am getting error for this piece CDate(r(“opened_at”).ToString)
It says Assign: Conversion from string “31-08-2016 15:17” to type ‘Date’ is not valid.

22

@niranjan_shetty

Please let me know the dateformat that you are getting is it always in the above format? If yes then use this instead of cdate

dtCsv.AsEnumerable.Where(Function(r) r("incident_state").ToString.Equals("New") AndAlso DateTime.ParseExact(r("opened_at").ToString,"dd-MM-yyyy HH:mm",System.Globalization.CultureInfo.InvariantCulture)>CDate(Latestdate).adddays(-x)).Count

Cheers