How to get the entire data with date reference

Help Automation Hub
,
1

how to get the data for that date with the date reference.
example: if the date is 28-04-2023
the data upto 28-04-2023 should get in the excel

2

Hi @anjani_priya

How about the following?

image
image757×667 23.9 KB

TargetDate=DateTime.ParseExact("28-04-2023", "dd-MM-yyyy", CultureInfo.InvariantCulture)

DT1=(From row In DT1.AsEnumerable()
               Let dateValue = DateTime.ParseExact(row.Field(Of String)("Date"), "dd-MM-yyyy", CultureInfo.InvariantCulture)
               Where dateValue <= targetDate
               Select row).CopyToDataTable()

Note: Datatype of TargetDate is System.DateTime

I/P:
image

O/P:
image

Hope this helps!!

3

You can please add some more context. You want to filter the Excel? Get data from somewhere?

If data is already there you can use Excel filter activity & use date in it as filter for the data.

4

Book1.xlsx (77.0 KB)
input sheet is the date reference
from input sheet mentioned date, the date of salary of the april month of IT dept should be sum at the input sheet beside of date.
how to do?

5

Hi @anjani_priya ,

Could you maybe check the below Expression :

DT.AsEnumerable.Where(Function(x)DateTime.ParseExact(dateValue,"d-M-yyyy",System.Globalization.CultureInfo.InvariantCulture).ToString("MMyy").Equals(CDate(x("month").ToString).ToString("MMyy")) andAlso x("dept").ToString.ToUpper.equals("IT")).Sum(Function(x)CDbl(x("salary").ToString))

From Debug & implementation :

image
image1165×342 16.5 KB

Here, dateValue is the Output of Read Cell activity where we are reading the Input Date from the first cell of input sheet and DT is the table data present in Excel input2 sheet.

Let us know if this is not the expected output and maybe explain a bit more on the required output representation.

6

the entire row with headers should be displayed

7

@anjani_priya ,

From this statement, we assumed you would require it as a Single value (Sum of Salary with Filters Added) and so the expression was provided accordingly.

Is it possible for you to provide a visual Representation ? A Screenshot or Excel sheet of the Expected Output format ?

8

Book1.xlsx (76.8 KB)
input input2 are inputs
output is the required and the salary should be sum and places beside the date, in input sheet and then convert that into crores
can you send the sample code
and if I change the date the respective date should be sum and should be print in the beside of date in input sheet

9

@anjani_priya ,

Could you maybe check with the below workflow :
Excel_FilterValues.zip (31.5 KB)

10

There is a possibility like if we change the date in input sheet the sum should also change.Is there a possibility like this?

11

@anjani_priya ,

For this case, after the Date has been changed a re-run of the workflow would need to be done so that it gets filtered/updated with the new date value.

For a Dynamic change on the Filtration maybe additional steps using Macro would need to be done.

13

if we want to pick data other dept also how to do?

14

@anjani_priya ,

You could replicate the same methods used in the workflow, There is a Linq Expression which uses the Comparison on dept column to retrieve IT value rows, you could change it to get the required department.

If still not able to perform we would ask you show us the output representation. (The Change in dept column value should work)

15

there are multiple sheets and multiple dept how to do then
Book1.xlsx (78.4 KB)
input sheet is to be filled
only april month data should be taken of all depts and should sum and print in input sheet in crores format.
can you tell how to do

16

can you tell how to solve

17

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.