topic Null - Not Null - Or? in QlikView

Null - Not Null - Or?

Thu, 12 Jan 2012 16:21:50 GMT

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,

Null - Not Null - Or?

Thu, 12 Jan 2012 16:25:00 GMT

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

Null - Not Null - Or?

Thu, 12 Jan 2012 17:10:07 GMT

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

Null - Not Null - Or?

Thu, 12 Jan 2012 17:13:34 GMT

did you try:

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

Null - Not Null - Or?

Thu, 12 Jan 2012 17:35:27 GMT

Yes, I tried that earlier and to no avail.

Re: Null - Not Null - Or?

Thu, 12 Jan 2012 17:52:11 GMT

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

Re: Null - Not Null - Or?

johnpaul — Thu, 12 Jan 2012 20:58:23 GMT

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?

Null - Not Null - Or?

Fri, 13 Jan 2012 03:54:51 GMT

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.

Null - Not Null - Or?

rohit214 — Fri, 13 Jan 2012 06:49:08 GMT

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

Null - Not Null - Or?

vijay_iitkgp — Fri, 13 Jan 2012 07:58:34 GMT

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

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

Null - Not Null - Or?

hic — Fri, 13 Jan 2012 10:49:35 GMT

Len(trim(Field2)) = 0 always does the trick – unless you have hard spaces in the string. These are not trimmed. In such a case you need to do Len(trim(purgechar(Field2, chr(160))))=0 instead.

Re: Null - Not Null - Or?

Fri, 13 Jan 2012 13:12:39 GMT

I will try this as it looks like there are 10 hard spaces there.

Thanks all for your replies, very helpful!

Sent from iPhone

Re: Null - Not Null - Or?

Mon, 27 Aug 2012 11:23:55 GMT

cust name amount

chandu 100

- 200

rajesh 500

-- 600

ramesh 700

- 800

- 900

- 100