I have file config and input as below.
File Config as below.
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
jack.chan
(Jack Chan)
March 20, 2022, 4:27am
4
Use my sequence for reference
TestA.xaml (13.0 KB)
read config and input to configDt and inputDt datatables
loop configDt , in each iteration, filter input Dt so that APAPDT >=APAPDT1 and <= APAPDT2 and output filtered table to filterdDt
write filteredDt to FileName.xlsx
Result
Report1.xlsx
Report2.xlsx
1 Like
system
(system)
Closed
March 23, 2022, 4:28am
5
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.