Null - Not Null - Or?

[#1]

I have a table with 2 fields, field1 and field 2.  Field 1 has 100 records, field 2 only has 75 records.  When field 2 is null, I want to assign a value to it but the 25 records without data show up with what appears to be a null value.   I have tried several things.  1st, when I do a len(field2) all of them show up as 10, even if they do not have data in them.  I have tried doing a trim on field 2 but it deletes the entire line leaving me with 75 records not 100.

How do I deal with a field that has no data in it, but has a length of 10?  I want the script to be, if Field2 is Null, assign Field1 as its value, else Field2.

Thanks,

[#2]

Why Dont you try applying the trim in the formula so you don't actually remove the row... eg

If (trim(len(field2)=0,Value,field2) as field3

This should work

[#3]

I gave it a go and it did not work, it still returned an empty field for field3

[#4]

did you try:

If (field2='',Value,field2) as field3

[#5]

Yes, I tried that earlier and to no avail.

[#6]

Maybe this example can be helpful. I put field1 in field 2 when field2 is null.

[johnpaul]

during the load script can you check for this blank field and use the Null() to set it to a null value?

What is actually in the field that you are loading?

Is it a CSV and have you used an Editor to check the value in that field?

[#8]

The earlier if test will perform incorrectly when your "empty" field contains blanks as yours probably does.

If(trim(len(field2)) = 0, Value, field2) AS field2

Will always return field2 AS field2 for you since len(field2) = 10 and trimming it has no effect.

The correct form is:

if(len(trim(field2)) = 0, value, field2) AS field2

Trim will trim away all blanks leaving a zero length field2 value and field2 will be filled by "value".

If those 25 values in field2 are not blanks but some other non-printable characters, this won't work but it is unlikely these values aren't 10 blanks.

[rohit214]

Hi try this

1. Inchart--(IF(LEN(FEILD)= 0 OR isnull(FEILD) ='-1',FIELD))

2. InScript--IF(LEN(FEILD)='0' OR ISNULL(FEILD) AS Flag1,

thanks

rohit

[vijay_iitkgp]

Hi, I think Dave has given correct answer to your question

Use  If(Len(Trim(Field2))=0,Field1,Field2) AS Field2