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:
- Missing Expiry Date
- Expired ID/Passport
- Mismatch of Citizenship and PassportCountry
- Missing Citizenship
Whats the fastest way to do these comparisons and to have a new datatable with added columns:
Stores False/True values if the passport is expired of expiration date is missing.
Stores False/True values if the Citizenship is missing or it does not match with
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.
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
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.
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.
@hurmet.noka , Have you Tried the Below Expression :
"[ExpirationDate] is Null"
More on DataColumn Expression :
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
Assign: Exception has been thrown by the target of an invocation.
Why is behaving like this, two DateTimes cant be compared ?
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
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 :
@hurmet.noka , If you do further Analysis, we could find that the
CurrentRow.Item("ExpirationDate") outputs a
Object Type and not it’s
Hence, We would require to Explicitly Convert the Value to It’s Required Type, For Date values, we use
DateTime.Parse() as applicable for the values.
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!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.