If statement
Altair EmployeeI am trying to do an "if" statement on a lookup field to return a calculate field of another. At the end of the day I have a "lookup" column that I created to extract out "CR" or "DB" from a string. The raw data has CR as positive and DR as negative in some but others DRs are positive. I have an Adj Amt column that has an dollar amt that I need to make lookup column containing CR neg and DB positive however in some instances the DB will need to stay negative and those are the ones with the Adj Amt such as 100.00-. The negative is on the end of the number not front. I tried this formula below and it didn't work. I have attempted to add an .Or. to that statement to keep those negative DR the same but cant get to that step until I get the CR working first. Any help would be appreciated!
if([lookup]="CR",[Adj Amt]*-1,[Adj Amt])
Lookup Adj Amt End result looking for Lookup Adj Amt
CR 100.00 CR -100.00
DR 200.00 DR 200.00
DR 150.00- DR -150.00
Answers
-
Hi Kimerly,
There are possibly two areas of complication here. The first is understanding the required logic. The second is that the negative is trailing. This is not a problem if it’s just showing as training in the report, as Monarch will read this correctly, but if you are still seeing a trailing minus in your table, then it is likely to be a character field.
Ignoring the second issue for a moment: Is it true to say that you want to swap the sign of the amount if the Lookup is CR, but leave it the same if it is DR? For example . . .
Lookup Orig Amnt New Amount
CR 100.00 -100.00
CR -200.00 200.00
DR 300.00 300.00
DR 400.00 400.00
If that assumption is correct and [Orig Amnt] is a numeric field, then your original formula should work. Or you could use
If([Lookup] = “CR”,-[Orig Amnt],[Orig Amnt])
If you are not able to click OK on that because of an error, then please check that the [Orig Amnt] is a numeric field. If it’s character, then you can either correct it in the table and try again or use the Val function to dynamically convert it to numeric as part of the expression.
If([Lookup] = “CR”,Val(-[Orig Amnt]),Val([Orig Amnt]))
If you are using Monarch Classic, please also check that the field type you are creating in the calculated field is set to Numeric in the general tab. You don’t need to do this for Data Prep Studio.
If you can OK it, but it just doesn’t give the results you need, then you may have leading spaces in front of the [Lookup]? You can test this by using the expression:
If(Trim([Lookup]) = “CR”,-[Orig Amnt],[Orig Amnt])
If I’m miles off track, please feel free to correct me and let me know the version of Monarch you are using.
Thanks,
Steve.
0 -
Altair Forum User said:
Hi Kimerly,
There are possibly two areas of complication here. The first is understanding the required logic. The second is that the negative is trailing. This is not a problem if it’s just showing as training in the report, as Monarch will read this correctly, but if you are still seeing a trailing minus in your table, then it is likely to be a character field.
Ignoring the second issue for a moment: Is it true to say that you want to swap the sign of the amount if the Lookup is CR, but leave it the same if it is DR? For example . . .
Lookup Orig Amnt New Amount
CR 100.00 -100.00
CR -200.00 200.00
DR 300.00 300.00
DR 400.00 400.00
If that assumption is correct and [Orig Amnt] is a numeric field, then your original formula should work. Or you could use
If([Lookup] = “CR”,-[Orig Amnt],[Orig Amnt])
If you are not able to click OK on that because of an error, then please check that the [Orig Amnt] is a numeric field. If it’s character, then you can either correct it in the table and try again or use the Val function to dynamically convert it to numeric as part of the expression.
If([Lookup] = “CR”,Val(-[Orig Amnt]),Val([Orig Amnt]))
If you are using Monarch Classic, please also check that the field type you are creating in the calculated field is set to Numeric in the general tab. You don’t need to do this for Data Prep Studio.
If you can OK it, but it just doesn’t give the results you need, then you may have leading spaces in front of the [Lookup]? You can test this by using the expression:
If(Trim([Lookup]) = “CR”,-[Orig Amnt],[Orig Amnt])
If I’m miles off track, please feel free to correct me and let me know the version of Monarch you are using.
Thanks,
Steve.
Hi Steve-
I am using Classic version. I checked my fields and tried both numeric and character and it is still giving me the red outline. The data that we are scraping comes in with the negative behind the Adj Amt. Most of the DR are positive but those that are DR with the Amt as a neg sign behind the amt needs to stay neg and we need to change all credits to a negative as well.
Lookup Orig Amt New Amt
CR 100.00 -100.00
CR 200.00 -200.00
DR 300.00- -300.00
DR 400.00 400.00
0 -
Altair Forum User said:
Hi Steve-
I am using Classic version. I checked my fields and tried both numeric and character and it is still giving me the red outline. The data that we are scraping comes in with the negative behind the Adj Amt. Most of the DR are positive but those that are DR with the Amt as a neg sign behind the amt needs to stay neg and we need to change all credits to a negative as well.
Lookup Orig Amt New Amt
CR 100.00 -100.00
CR 200.00 -200.00
DR 300.00- -300.00
DR 400.00 400.00
Hi,
If the [Orig Amnt] is coming in to the table with trailing minus, then my guess would be that it is being detected as a character field. You should be able to verify this from the field list or by looking at the status line at the bottom of the table when that field is highlighted.
In any case, I think getting a simplified expression without introducing the IF() expression would clarify the situation.
First, create a calculated field making sure that the field type is Numeric. Just try a simple expression like 1+1 to test that you don’t have any software or systems issues. If you don’t get a column containing 2, then something is very wrong and it would be worth getting in touch with tech support!
Then change the expression to [Adj Amnt]. If 1+1 is OK, but [Adj Amnt] isn’t, then it must be a character field. You can either change it to Numeric or use the expression Val([Adj Amnt]).
Once you are getting the duplicated [Adj Amnt], I think the If() statement in my previous post will work with the samples you gave.
If([Lookup] = “CR”,-[Orig Amnt],[Orig Amnt])
Lookup Orig Amt New Amt
CR 100.00 -100.00
CR 200.00 -200.00
DR 300.00- -300.00
DR 400.00 400.00
However, if you could potentially have a negative credit, then the sign will be swapped to a positive. I don’t know if this is possible or desirable?
CR 200.00- 200.00
If you want all CR to be negative, regardless of the original sign, then I think the Absolute function will help.
If([Lookup] = “CR”,-Abs([Orig Amnt]),[Orig Amnt])
Regards,
Steve.
0