(Prepare Recipe) Formula Processor for range values in column of numerical and timestamp format.

[gerryleonugroho]

So, I'm trying to create a column based on another column values, first would be on the numerical range column case I'm having.

As per the following explanation :

column_a
0
1
2
3
4
5
...
100

My objective is trying to create another column, let's just call it column_b, I'm aware, I could achieve this also through the mean of the bin recipe processor, but I wish to achieve them, through the formula processor instead.

So column_a consist of values ranging from 0 to 100. And I wish to create another column based on the range of values from the column_a. I've been having problem, since my code wouldn't work, as per the following example:

if (column_a <= 15, 0,
if(column_a > 16 && column_a <= 30,  1,
if(column_a > 31 && column_a <= 45,  2,
if(column_a > 46 && column_a <= 60,  3,
if(column_a > 61, 4)
))
)

 I wish to assign value to column_b, per range of values coming from column_a.

- if value less or equal to 15, then assign value of 0

- if value greater then 16, but less or equal to 30, assign 1 and so forth.

And my second problem would still be similar, but this time with the Timestamp format, I wish to achieve similar table output but with different data format. As the following:

timestamp_a

2015-01-01T00:00:00.000Z

2015-01-02T00:00:00.000Z

2015-01-03T00:00:00.000Z

2015-01-04T00:00:00.000Z

2015-01-05T00:00:00.000Z

2015-01-06T00:00:00.000Z

2015-01-07T00:00:00.000Z

....

2020-05-31T00:00:00.000Z

So I wish to achieve somewhat similar output, by assigning a value to another column based on the range value from the timestamp_a, as per the following:

if (timestamp_a <= “2016-01-01T00:00:00.000Z”, 0, 
if (timestamp_a > “2016-01-01T00:00:00.000Z” && timestamp_a <= “2016-12-31T00:00:00.000Z”,  1,  
if (timestamp_a > “2017-01-01T00:00:00.000Z” && timestamp_a <= “2017-12-31T00:00:00.000Z”,  2,
if (timestamp_a > “2018-01-01T00:00:00.000Z” && timestamp_a <= “2018-12-31T00:00:00.000Z”,  3,
if (timestamp_a > “2019-01-01T00:00:00.000Z”, 4)
))
)

And yes, I'm aware I could do this using another processor recipes that related to date manipulation, but I simply wish to achieve this through the mean of formula based one instead. Wish to assign new column values, based on the range of 

Thanks for all the input.

Cheers,

[Liev]

Hi @gerryleonugroho 

There was a small issue in your formula. The last IF statement was missing the else clause, I think there was also a missing closing bracket.

if(column_a <= 15, 0,
   if(column_a > 15 && column_a <= 30,  1,
      if(column_a > 30 && column_a <= 45,  2,
         if(column_a > 45 && column_a <= 60,  3,
            if(column_a > 60, 4, "")
            )
        )
     )
  )

An alternative way of doing this, since your conditions are incremental, is to start from the end and work your way backwards. This will lead to a less verbose option, have a look as they produce the same outputs.

if(column_a > 60, 4, 
   if(column_a > 45, 3, 
      if(column_a > 30, 2,
         if(column_a > 15, 1, 0)
         )
      )
  )

[gerryleonugroho]

Hi @Liev, thanks. I also like the second option workaround better, really appreciate it. Would the solution also work on my timestamp data format too?

Thanks,