Hi, could any one assist why I am getting this error.
I have changed the column No. to string. Here's the expressions used:
IIF([Row-1:No.]+1=[No.],[Row-1:New Field] , [Row-1:New Field]+1)
I am doing sequential check.
Thank you.
Solved! Go to Solution.
Hey @dhakim1996,
You cant add one +1 to text data (string data) you can only do that to numeric data. Ether convert it back to a number before this or you can use the to Number function like this:
IIF(toNumber([Row-1:No.])+1=toNumber([No.]),[Row-1:New Field] , [Row-1:New Field]+1)
Hi @IraWatt
Thank you for the quick reply! It works now however it is not returning the desired results as this was what I wanted:
No. | NewField |
20220001 | - |
20220002 | 1 |
20220004 | 2 |
20220006 | 2 |
The new field is a sequential testing that would return the difference between the previous row. Anything that is more than 1 I would like to flag it out.
Hey @dhakim1996, I think I get what you want to achieve. Would you be able to give the result you wanted in the table above?
Hi @IraWatt sorry I dont get what you mean by giving the results I wanted? 😅
@dhakim1996 ah sorry I thought the table you have above was the results you got from your multi row Tool. I take it this is the results you where wanting to get out of the tool:
No. | NewField |
20220001 |
- |
20220002 | 1 |
20220004 | 2 |
20220006 | 2 |
Are you just calculating the difference between the two rows here? You said anything that is more than 1 I would like to flag it out, what does this mean? The first two rows are less then 1 difference and I don't see the difference between them and the other rows?
Yes @IraWatt I would to get a difference between two rows. Any difference of more than 1 between two rows I would like to subsequently filter out. However, how do I calculate the difference between 2 rows?
@dhakim1996 I would use a formula like this:
IF [Row-1:No.] = NULL() THEN NULL() ELSE
toNumber([No.])-toNumber([Row-1:No.])
ENDIF
The first bit checks that the row above exists if it doesn't it just fills the value with NULL else minus the current row with the one above.
The community has some quick and easy videos on formulas and the Formula Tool here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Writing%20...
Is this what you want?