Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Every [part number] should have a [Commodity Code] however when the [Commodity Code] is blank, isnull, or [Commodity Code]=null() or if(len([Commodity Code])<2,1,0) doesnt work.
Please see my qvw attached.
Only an idea: If you might have varying number of spaces, you could use one of the trim functions:
=if(trim([Commodity Code])='','BLANK',[Commodity Code])
Hi Joseph,
For the [part numbers] that appear to return no [Commodity Code] in the data, it looks like in fact they are returning 3 spaces instead.
As a result of the rows containing 3 spaces, this is why they are not satisfying the isnull() function or the
if(len([Commodity Code])<2,1,0).
Perhaps try
if([Commodity Code])=' ',1,0) using 3 spaces in between the ' '
Have attached a table of your data...
Hope this helps?
Kind regards,
Rich
Only an idea: If you might have varying number of spaces, you could use one of the trim functions:
=if(trim([Commodity Code])='','BLANK',[Commodity Code])
when i used =if(len(trim([Commodity Code]))<2'','BLANK',[Commodity Code])
it worked. Also, RichSheppard's input was very helpfuul and explains why my original formula didnt work.