Extract Years from multiple columns and prepare a consolidated list

Hi Sunil,

get the Start_Date and End_Date into two arrays and then apply UNION function on those.

For more details plz go thru the below link:

docs.appian.com/.../fnc_set_union.html

Thanks,
Nitesh

Hi Sunil.

Are you trying to implement this logic in the appian side or the DB side. If it is Appian side the other partitioner solution would work. if it is in DB side you implement any SP for that.

Could you please explain more about your use case?

code sample of nitesht's answer to solve this in SAIL. 

 

If you meant you wanted all unique years from both start and end dates together:

load(
  local!startYears: {2011, 2012, 2012, 2013, 2028}, 
   /*use index(ri!employee, "Start_Date", {}) here */
  local!endYears: {2013, 2025, 2028, 2046}, 
   /*use index(ri!employee, "End_Date", {}) here */
  local!allYears: append(local!startYears, local!endYears),
  local!allUniqueYears: union(local!allYears, local!allYears),
  local!allUniqueYears
)

 

Similar code if you meant you wanted the unique start years and unique end years separately:

load(
  local!startYears: {2011, 2012, 2012, 2013, 2028}, 
  /* use index(ri!employee, "Start_Date", {}) here */
  local!endYears: {2013, 2025, 2028, 2046, 2046, 2046}, 
  /* use index(ri!employee, "End_Date", {}) here */
  local!startUniqueYears: union(local!startYears, local!startYears),
  local!endUniqueYears: union(local!endYears, local!endYears),
  /*local!startUniqueYears*/
  local!endUniqueYears
)

Hi Sunil,

There are 2 steps to this solution:
1. Get the list of all the years, given the Start_Date and End_Date fields
2. Get the distinct years from the list yielded by step 1.

Details:

1. You can use the year() function to extract the year as an Integer from a Date. To do this over an array of dates, a!forEach should work fine.

2. A common method to get the distinct values (remove duplicates) from a list is to use a union() function, using the same list as both arguments. This function will return the list without duplicates. It is a good idea to make a general "GBL_distinct" rule to encapsulate this purpose.

Thanks All for your suggestions, I have completed the case with all your tips. Thanks Once again...