Total summary as per Insurance names

[Jeeru_venkat_Rao]

Hi Friends,

I am looking for linq queries which can give me summary like below screenshot. Sheet1 have data which include insurance name and botstatus and I want output like in summary sheet.

Sheet1

Summary

Basically in summary we are showing insurance total, exception, processed count as per date. Today we are running bot and bot status will save in sheet1 so from that I need to create summary.
summary query.xlsx (9.3 KB)

@rashmisingh2695

Pls help

[rashmisingh2695 (Rashmi)]

Hi @Jeeru_venkat_Rao

Try to use this in invoke code activity

Dim insuranceDict As New Dictionary(Of String, Dictionary(Of String, Integer))()

For Each row As DataRow In dtInput.Rows
    Dim insuranceName As String = row("Insurance Name").ToString()
    Dim botStatus As String = row("Bot Status").ToString()

    If Not insuranceDict.ContainsKey(insuranceName) Then
        insuranceDict(insuranceName) = New Dictionary(Of String, Integer) From {{"Processed", 0}, {"Exception", 0}}
    End If
	
    insuranceDict(insuranceName)(botStatus) += 1
Next

For Each kvp As KeyValuePair(Of String, Dictionary(Of String, Integer)) In insuranceDict
    Dim insuranceName As String = kvp.Key
    Dim processedCount As Integer = kvp.Value("Processed")
    Dim exceptionCount As Integer = kvp.Value("Exception")
    Dim totalCount As Integer = processedCount + exceptionCount
    dtSummary.Rows.Add(Now.ToString("dd-MM-yyyy"), insuranceName, totalCount, exceptionCount, processedCount)
Next

[Jeeru_venkat_Rao]

Can I get total of each column in bottom? If now pls add that one as well.

Eg.

[rashmisingh2695 (Rashmi)]

Use this code to get total of each column in bottom

Dim insuranceDict As New Dictionary(Of String, Dictionary(Of String, Integer))()

For Each row As DataRow In dtInput.Rows
    Dim insuranceName As String = row("Insurance Name").ToString()
    Dim botStatus As String = row("Bot Status").ToString()

    If Not insuranceDict.ContainsKey(insuranceName) Then
        insuranceDict(insuranceName) = New Dictionary(Of String, Integer) From {{"Processed", 0}, {"Exception", 0}}
    End If
	
    insuranceDict(insuranceName)(botStatus) += 1
Next

Dim totalPatients As Integer = 0 
Dim totalExceptions As Integer = 0 
Dim totalProcessed As Integer = 0

For Each kvp As KeyValuePair(Of String, Dictionary(Of String, Integer)) In insuranceDict
    Dim insuranceName As String = kvp.Key
    Dim processedCount As Integer = kvp.Value("Processed")
    Dim exceptionCount As Integer = kvp.Value("Exception")
    Dim totalCount As Integer = processedCount + exceptionCount

    dtSummary.Rows.Add(Now.ToString("dd-MM-yyyy"), insuranceName, totalCount, exceptionCount, processedCount)
	
	totalPatients += totalCount 
	totalExceptions += exceptionCount 
	totalProcessed += processedCount
Next

dtSummary.Rows.Add("Total","", totalPatients, totalExceptions, totalProcessed)

[Jeeru_venkat_Rao]

@rashmisingh2695 -Thank you so much for your quick response.

Its working perfectly. I shows 2 insurance name for upper & lower. I made some changes and now its working and give correct results

[Jeeru_venkat_Rao]

@rashmisingh2695 – Today I noticed that date showing in string format in bot status. I tried to modify it but success.
Like in 2nd line 01/08/2025. Can we do it like 1st line format?

I have to compare with other dates here it not compare just becz of string format.

[rashmisingh2695 (Rashmi)]

Hi @Jeeru_venkat_Rao

You can try to modify the below line of code. So, that Bot run date format is like 01/08/2025 00:00:00

dtSummary.Rows.Add(Now.ToString("MM/dd/yyyy 00:00:00"), insuranceName, totalCount, exceptionCount, processedCount)

[Jeeru_venkat_Rao]

@rashmisingh2695 - Thank you so much. Its working.