Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to create a new column which creates a "ready" or "not ready" status based on certain conditions. However the conditions in my code currently don't give the correct results so something did go wrong.
This is my code:
IF(len(trim(Theme))*len(trim(Distr))*len(trim(MovAv.4200))*len(trim("CoO(Purcha"))*len(trim("LSRP-4000"))>0,'Ready','Not ready') as SAPSFMSstatusSalesorder
And this is what I want to establish
Len() applies to the text representation of a value, which means len(0) for example will return 1 (and Len(0.00) will return 4). You'll need to test for zero values separately and not using len(), using e.g. Value<>0
You should be able to use Len(Trim(String))=0 to remove blanks and nulls, barring special cases. If you define certain cases as "blank" even though they have a non-display character, such as Carriage Return, this may not work as expected.
If a field is numeric, you just need to check if it is <>0 (in addition to possibly checking for nulls). If it is a string, you will need quotes - '0,00' - to check for the values in question. Alternatively, if it's a string which is meant to display numeric values, you can convert it to a number using num#().
num#(Value,'#,00') should work just fine.
If you correctly set your separators to match the data, this should get picked up as a number without requiring an explicit conversion, most likely. e.g.
SET ThousandSep='.';
SET DecimalSep=',';
There would be no practical difference between the two num#() formats. You should be able to use either one here.
I can't really make sense of your statement, I'm afraid. You're looking for something like, I think:
If(num#(Field,'#,00')<>0 AND num#(Field2,'#,00')<>0 AND Some Other Conditions Here, 'Status1', 'Status2')
Len() applies to the text representation of a value, which means len(0) for example will return 1 (and Len(0.00) will return 4). You'll need to test for zero values separately and not using len(), using e.g. Value<>0
Ok thanks for the directions @Or I made below now. And have the following questions:
IF(len(trim(Theme))*len(trim(Distr))*len(trim(MovAv.4200))*len(trim("CoO(Purcha"))*len(trim("LSRP-4000"))>0,'Ready','Not ready') And IF((Distr)<>0,'Ready','Not ready') And IF(([MovAv.4200])<>"",'Ready','Not ready') And IF((MovAv.4200)<>0.00,'Ready','Not ready') And IF(("LSRP-4000")<>0.00,'Ready','Not ready') as SAPSFMSstatusSalesorder
You should be able to use Len(Trim(String))=0 to remove blanks and nulls, barring special cases. If you define certain cases as "blank" even though they have a non-display character, such as Carriage Return, this may not work as expected.
If a field is numeric, you just need to check if it is <>0 (in addition to possibly checking for nulls). If it is a string, you will need quotes - '0,00' - to check for the values in question. Alternatively, if it's a string which is meant to display numeric values, you can convert it to a number using num#().
Thanks @Or and what to do with values in the dataset that are 0,00 and which are numbers? The script doesn’t take the commma
num#(Value,'#,00') should work just fine.
If you correctly set your separators to match the data, this should get picked up as a number without requiring an explicit conversion, most likely. e.g.
SET ThousandSep='.';
SET DecimalSep=',';
OK thanks @Or , num#(Value,'#,00') should not be num#(Value,'0,00') ?
I now constructed this code: IF(len(trim(Theme))*len(trim(Distr))*len(trim(MovAv.4200))*len(trim("CoO(Purcha"))*len(trim("LSRP-4000"))>0,'Ready','Not ready') And IF((Distr)<>'0','Ready','Not ready') And IF(([MovAv.4200])<>"",'Ready','Not ready') And IF(num#([MovAv.4200],'#,00') ,'Ready','Not ready') And IF(num#("LSRP-4000",'#,00') ,'Ready','Not ready') AND IF((["LSRP-4000"])<>0,'Ready','Not ready') as SAPSFMSstatusSalesorder
For this what I would like to establish:
Is the code in general correct?
It does give me an error with loading, although it doesn't give any errors in the script itself
There would be no practical difference between the two num#() formats. You should be able to use either one here.
I can't really make sense of your statement, I'm afraid. You're looking for something like, I think:
If(num#(Field,'#,00')<>0 AND num#(Field2,'#,00')<>0 AND Some Other Conditions Here, 'Status1', 'Status2')
The formula seems to work. I still want to test it with the 0 values, since the 0 values were not in my dataset. Once I have tested I will let you know