Filtering Data Table with other excel value

Hi All

I have 2 Excel one is real excel and second is failed excel. I want to extract data from excel(Real) on the behalf of real excels time in state and I want it to create another violation excel report if the value of threshold field is grater than the time in state.
Real.xlsx (9.6 KB)
Failed.xlsx (10.3 KB)

Hi @Himanshu_Pratap_Rana

You already provide the input, can you provide also an expected output?

Regards!

Output.xlsx (9.3 KB)

Do you mean you want to filter out the data where Time in State is greater than Threshold Failed?

1 Like

Hi @Himanshu_Pratap_Rana

Give a try with

dtResult =

(From r In dtInput
Join d In dt2
On r("Actual State").ToString().ToUpper() Equals d("Actual State").ToString().ToUpper()
Let State = DateTime.ParseExact(d("Time in State").ToString(),{"hh:mm", "hh:mm:ss", "hhmmss", "hhmm", "h:mm", "h:m", "hmm", "HH:mm", "H:mm"},System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString("HH:mm")
Let Threshold = DateTime.ParseExact(d("Threshold Failed").ToString(),{"hh:mm", "hh:mm:ss", "hhmmss", "hhmm", "h:mm", "h:m", "hmm", "HH:mm", "H:mm"},System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString("HH:mm")
Where State > Threshold
Let ra = New Object(){d("Actual State"), d("Time in State"), d("Threshold Failed")}
Select dtResult.Rows.Add(ra)).CopyToDataTable()

image

Regards

2 Likes

Can you send the sequence if possible?

Hi @Himanshu_Pratap_Rana

Here you go
FilterDTStarterHelper.zip (18.0 KB)

Regards!


Why this error coming?

Hi @Himanshu_Pratap_Rana

make sure to import the linq name space
image

Regards


Same error

I would ask for help in this case, dunno what could be the reason

@ppr, @Anil_G, @Yoichi, Any inputs about the above error? (definition of method ‘join’ is not accessible in this context UiPath)

Regards

@Himanshu_Pratap_Rana

Looks like the reference is missing…please check if the reference is present by opening the xaml in notepad…please try renaming project.json also and reopen main and check

Can you attach the complete project here please

@fernando_zuluaga did you happen to send whole project? Or only a xaml

Cheers

Hi @Anil_G

I attached the whole project, that is the weird thing

@fernando_zuluaga

One possible reason could be the version differences and references might have been missed…

I see @Himanshu_Pratap_Rana versions are 22.10.x and i guess you did on community with latest versions 23.x which would have caused this reference issue

Cheers

we should always use AsEnumerable when using LINQ and Datatables

dtInput.AsEnumerable()
dt2.AsEnumerable()

2 Likes

Hi @Himanshu_Pratap_Rana

Kindly replace the filter code in the assing activity to this new code

(From r In dtInput.AsEnumerable()
Join d In dt2.AsEnumerable()
On r("Actual State").ToString().ToUpper() Equals d("Actual State").ToString().ToUpper()
Let State = DateTime.ParseExact(d("Time in State").ToString(),{"hh:mm", "hh:mm:ss", "hhmmss", "hhmm", "h:mm", "h:m", "hmm", "HH:mm", "H:mm"},System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString("HH:mm")
Let Threshold = DateTime.ParseExact(d("Threshold Failed").ToString(),{"hh:mm", "hh:mm:ss", "hhmmss", "hhmm", "h:mm", "h:m", "hmm", "HH:mm", "H:mm"},System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString("HH:mm")
Where State > Threshold
Let ra = New Object(){d("Actual State"), d("Time in State"), d("Threshold Failed")}
Select dtResult.Rows.Add(ra)).CopyToDataTable()

Regards