Datatable Check Missing Value, Check Expired Date, compare two columns

Help Studio
, , ,
1

Hello guys,

I have a Datatable extracted using a query from a database with the following columns:
Name, PassportNumber, ExpirationDate, Citizenship, PassportCountry

Now my duty is to check:

  1. Missing Expiry Date
  2. Expired ID/Passport
  3. Mismatch of Citizenship and PassportCountry
  4. Missing Citizenship

Whats the fastest way to do these comparisons and to have a new datatable with added columns:

  1. Missing/Expired Date
    Stores False/True values if the passport is expired of expiration date is missing.
  2. Missing/Mismatch Citizenship
    Stores False/True values if the Citizenship is missing or it does not match with
    PassportCountry.

Should I go with add column activitiy and then for each loop checking each value or is there a faster way, if yes whats the conditions.

Thank you so much and wish you a good day ahead. :sun_with_face:

2

Hi @hurmet.noka ,

Do Check the Below for more methods on Updating Datatable Columns :

Do Check first if the Data Column Expression Approach can be used in your Scenario. It does seem that you need True/False as Output Column Values, which might be easier to use with this method.

If this Method doesn’t provide the required result, let us know we could maybe provide a Linq Query which would be an alternate method.

1 Like
3

image
image887Ă—870 126 KB

Thank you @supermanPunch
What would be the expression in this scenario, [ExpirationDate] to check if its null or empty ?
Otherwise will go for the long way for each method.

4

@hurmet.noka , Have you Tried the Below Expression :

"[ExpirationDate] is Null"

More on DataColumn Expression :

1 Like
5

Amazing, will try it now.
Expiration Date hold Date time values, how can i compare it with now:

datetime.ParseExact(CurrentRow.Item(“ExpirationDate”).ToString, “dd/mm/yyyy”,system.Globalization.CultureInfo.InvariantCulture) < Now.AddDays(3)

Using this i get an error, string is not recognized as a valid DateTime

6

Doest work
Assign: Exception has been thrown by the target of an invocation.

7

image

Why is behaving like this, two DateTimes cant be compared ?

8

@hurmet.noka ,

Could you Show us your Implementation on DataColumn Expression ?

This Error Maybe because of the way the date value is formatted in the datatable. Normally the format would be "MM/dd/yyyy HH:mm:ss".

Hence, You could Check the Below Expression :

datetime.ParseExact(CurrentRow.Item("ExpirationDate").ToString, "MM/dd/yyyy HH:mm:ss",system.Globalization.CultureInfo.InvariantCulture) < Now.AddDays(3)

If the above doesn’t work, we would need to know the format of the date value Either using Debug or Write Line Activity by Specifying CurrentRow.Item("ExpirationDate").ToString as the Value.

Since this is used in a For Each Row Loop, there is a Possibility that the Date Column value can be empty as well. Use an If Activity to Check the Condition is true like below :

String.IsNullOrWhiteSpace(CurrentRow.Item("ExpirationDate").ToString)
1 Like
9

@hurmet.noka , If you do further Analysis, we could find that the CurrentRow("ExpirationDate") or CurrentRow.Item("ExpirationDate") outputs a Object Type and not it’s DataColumn type.
image

Hence, We would require to Explicitly Convert the Value to It’s Required Type, For Date values, we use DateTime.ParseExact(), CDate(), DateTime.Parse() as applicable for the values.

1 Like
10

image

this is MM, dd, yyyy

datetime.ParseExact(CurrentRow.Item(“ExpirationDate”).ToString, “MM/dd/yyyy HH:mm:ss”,system.Globalization.CultureInfo.InvariantCulture) < Now.AddDays(3)

This Works! Thank you so much sir!

1 Like
11

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