parsing a date array

I have an array that contains a number of dates. I want to provide a month i.e 11 and have Appian return the date for the next month.

i.e

List of Timestamp: 12 items
24/04/2018 12:00 BST 
29/05/2018 12:00 BST
26/06/2018 12:00 BST
24/07/2018 12:00 BST
21/08/2018 12:00 BST
18/09/2018 12:00 BST
16/10/2018 12:00 BST
13/11/2018 12:00 GMT
11/12/2018 12:00 GMT
29/01/2019 12:00 GMT
26/02/2019 12:00 GMT
26/03/2019 12:00 GMT

 

Provide '10' and have as a return 

13/11/2018 12:00 GMT

 

I've tried using wherecontains to get the index but it only matches if i provide a full string (16/10/2018 12:00 BST ), not a subset. 
Any ideas ?

  Discussion posts and replies are publicly visible

  • If the array is always sorted properly, just increment the index of the returned value from the wherecontains.
  • 0
    Certified Lead Developer

    Hi Pauls0003,
    The following code should serve your purpose.

    convert your array using touniformString() function and then this code will work for your case.

    load(
    local!dates: {
    "24/04/2018 12:00 BST",
    "29/05/2018 12:00 BST",
    "26/06/2018 12:00 BST",
    "24/07/2018 12:00 BST",
    "21/08/2018 12:00 BST",
    "10/09/2018 12:00 BST",
    "16/10/2018 12:00 BST",
    "13/11/2018 12:00 GMT",
    "11/12/2018 12:00 GMT",
    "29/01/2019 12:00 GMT",
    "26/02/2019 12:00 GMT",
    "26/03/2019 12:00 GMT"
    },
    local!findValue: 10,
    index(
    local!dates,
    index(
    where(
    a!forEach(
    local!dates,
    toboolean(
    find(
    local!findValue,
    fv!item,
    2
    )
    )
    )
    ),
    1,
    - 1
    ) + 1,
    null
    )
    )


    Please let me know if this helps.

  • 0
    Certified Senior Developer
    in reply to harishkumart
    Hi,

    Its not working for me,

    lets suppose if you take the local!findvalue as 12 then its taking the first index and giving me the date "29/05/2018 12:00 BST"
  • 0
    Certified Lead Developer
    Hi Pauls,

    Please check with this code and this should work for you.

    /*To get all the instances*/
    load(
    local!dates: {
    "24/04/2018 12:00 BST",
    "29/05/2018 12:00 BST",
    "26/06/2018 12:00 BST",
    "24/01/2018 12:00 BST",
    "21/08/2018 12:00 BST",
    "10/04/2018 12:00 BST",
    "16/10/2018 12:00 BST",
    "13/02/2018 12:00 GMT",
    "11/12/2018 12:00 GMT",
    "29/01/2019 12:00 GMT",
    "26/02/2019 12:00 GMT",
    "26/03/2019 12:00 GMT"
    },
    local!findValue: 04,
    index(
    local!dates,
    where(
    a!forEach(
    local!dates,
    tointeger(
    right(
    left(
    fv!item,
    5
    ),
    2
    )
    ) = local!findValue
    )
    )+1,
    null
    )
    )
    /* To get the first instances*/
    load(
    local!dates: {
    "24/04/2018 12:00 BST",
    "29/05/2018 12:00 BST",
    "26/06/2018 12:00 BST",
    "24/07/2018 12:00 BST",
    "21/04/2018 12:00 BST",
    "10/09/2018 12:00 BST",
    "16/10/2018 12:00 BST",
    "13/11/2018 12:00 GMT",
    "11/12/2018 12:00 GMT",
    "29/01/2019 12:00 GMT",
    "26/02/2019 12:00 GMT",
    "26/03/2019 12:00 GMT"
    },
    local!findValue: 06,
    index(
    local!dates,
    index(
    where(
    a!forEach(
    local!dates,
    tointeger(
    right(
    left(
    fv!item,
    5
    ),
    2
    )
    ) = local!findValue
    )
    ),
    1,
    - 1
    ) + 1,
    null
    )
    )

    Please let me know if this works.
  • Hi,

    Pass the rule input value in this code as ri variable , try this code e,g ri!ip = 07
    load(
    local!dates: {
    "24/04/2018 12:00 BST",
    "29/05/2018 12:00 BST",
    "26/06/2018 12:00 BST",
    "24/07/2018 12:00 BST",
    "21/04/2018 12:00 BST",
    "10/09/2018 12:00 BST",
    "16/10/2018 12:00 BST",
    "13/11/2018 12:00 GMT",
    "11/12/2018 12:00 GMT",
    "29/01/2019 12:00 GMT",
    "26/02/2019 12:00 GMT",
    "26/03/2019 12:00 GMT"
    },
    local!findValue: ri!ip,

    local!indexodmonth:wherecontains(local!findValue,tointeger(right(left(local!dates,5),2))),
    local!dateofrequiredmonth:index(local!dates,local!indexofmonth),
    local!dateofrequiredmonth
    )

    Hope it will work for you.
    Thanks,
    Neha Dangi
  • Try this

    load(
    local!findMonth: 3,
    local!dates: {
    "24/04/2018 12:00 BST",
    "29/05/2018 12:00 BST",
    "26/06/2018 12:00 BST",
    "24/07/2018 12:00 BST",
    "21/08/2018 12:00 BST",
    "18/09/2018 12:00 BST",
    "16/10/2018 12:00 BST",
    "13/11/2018 12:00 GMT",
    "11/12/2018 12:00 GMT",
    "29/01/2019 12:00 GMT",
    "26/02/2019 12:00 GMT",
    "26/03/2019 12:00 GMT",

    },
    local!dateIndex: where(
    a!forEach(
    items: local!dates,
    expression: mid(
    fv!item,
    4,
    2
    ) = text(
    local!findMonth,
    "00"
    )
    )
    ) + 1,

    index(
    local!dates,
    if(local!index = 13, 1, local!index)
    )
    )
  • Hi,

    hope the below code help,

    =load(   
        
        local!dates : {List to DateTime},
        
        local!findValue: 12,
        
        index(
          local!dates,
          wherecontains(
            if(
              local!findValue = 12,
               1,
               local!findValue + 1
             ),
            month(local!dates)
          ),
          null
        )
        
    )

     

    Thanks

  • You may try the following expression rule -

    Use: itemIndex as a rule input (integer)

    = load(
    local!allDates: {
    "24/04/2018 12:00 BST",
    "29/05/2018 12:00 BST",
    "26/06/2018 12:00 BST",
    "24/07/2018 12:00 BST",
    "21/08/2018 12:00 BST",
    "18/09/2018 12:00 BST",
    "16/10/2018 12:00 BST",
    "13/11/2018 12:00 GMT",
    "11/12/2018 12:00 GMT",
    "29/01/2019 12:00 GMT",
    "26/02/2019 12:00 GMT",
    "26/03/2019 12:00 GMT"
    },
    local!appianDates: a!forEach(
    items: local!allDates,
    expression: todate(
    split(
    fv!item,
    "/"
    )[2] & "/" & split(
    fv!item,
    "/"
    )[2] & "/" & split(
    fv!item,
    "/"
    )[3]
    )
    ),
    local!searchIndex: if(
    or(
    rule!APN_isBlank(
    ri!itemIndex
    ),
    ri!itemIndex <= 0,
    ri!itemIndex >= max(
    month(
    local!appianDates
    )
    )
    ),
    1,
    wherecontains(
    ri!itemIndex + 1,
    month(
    local!appianDates
    )
    )
    ),
    index(
    local!allDates,
    local!searchIndex,
    null
    )
    )