Filter column by condition?

I have file config and input as below.

File Config as below.
image

Config.xlsx (8.7 KB)

And file Input as below.


input22.xlsx (9.4 KB)

I want to filter column APAPDT in file input by read condition by file config
Condition : Column APAPDT (format = yyyyddmm)
value input >= APAPDT1 and value input <= APAPDT2

I want output after filter save in filename follow filename in column File Name in file config.
My output as below.
Report1.xlsx (9.0 KB)

Please guide me about it.

  • remark : My real data have input 300K rows

this might be useful for you

Hi @fairymemay ,

Is the format accurate, or is it yyyyMMdd ->(YearMonthDay)?

I have developed a sequence, but I’m doubtful about the outcome, so please let me know.

Declare a Dictionary of String, Tuple(Of DateTime, Datetime)

dt_excelConfig.AsEnumerable().ToDictionary(Function(k) k("File Name").ToString.Trim, 
	Function(v) Tuple.Create(
		DateTime.ParseExact(v("APAPDT1").ToString.Trim,str_dateFormat,Nothing),
		DateTime.ParseExact(v("APAPDT2").ToString.Trim,str_dateFormat,Nothing)
		)
	)

Filter values and assign to list to check if it contains values before assigning it to DataTable →

dt_excelInput.AsEnumerable().Where(Function(w) _
	dict_configData(key).Item1 < DateTime.ParseExact(w("APAPDT").ToString.Trim,str_dateFormat,Nothing) AndAlso
	dict_configData(key).Item2 > DateTime.ParseExact(w("APAPDT").ToString.Trim,str_dateFormat,Nothing)).CopyToDataTable()

CreateReportBasedOnDates.xaml (12.6 KB)

Kind Regards,
Ashwin A.K

Use my sequence for reference
TestA.xaml (13.0 KB)

  1. read config and input to configDt and inputDt datatables

  2. loop configDt, in each iteration, filter input Dt so that APAPDT >=APAPDT1 and <= APAPDT2 and output filtered table to filterdDt

  3. write filteredDt to FileName.xlsx

Result
Report1.xlsx


Report2.xlsx

1 Like

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