Conditions (removing zero, blanks) to create a new column

[KirstenKa]

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

• Theme-> text values,  blanks should be removed
• Distr -> text values,  0 should be removed
• MovAv.4200 -> numbers (with 2 decimals -> 0,00), 0,00 and blanks should be removed
• CoO(Purcha -> text values,  blanks should be removed
• LSRP-4000 -> numbers (with 2 decimals -> 0,00), 0 should be removed

[Or · Accepted solution]

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

[KirstenKa]

Ok thanks for the directions @Or   I made below now. And have the following questions:

• Where I have  text values,  and blanks should be removed -> can I stick with the  Len code?
• If I have text and need to have 0 removed is it correct like written below for Distr? ->  And IF((Distr)<>0,'Ready','Not ready') 
• For  MovAv.4200 and LSRP-4000 the values are 0,00 but qlik doesn't accept them because when there is a comma there starts a new lin, how to do that?

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

[Or · Accepted solution]

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#().

[KirstenKa]

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

[Or · Accepted solution]

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=',';

[KirstenKa]

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:

• Theme-> text values,  blanks should be removed
• Distr -> text values,  0 and blanks should be removed
• MovAv.4200 -> numbers (with 2 decimals -> 0,00), 0,00 and blanks should be removed
• CoO(Purcha -> text values,  blanks should be removed
• LSRP-4000 -> numbers (with 2 decimals -> 0,00), 0,00 AND 0 should be removed

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

[Or · Accepted solution]

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')

[KirstenKa]

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