Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Null - Not Null - Or?


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,

12 Replies
Not applicable

Null - Not Null - Or?

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

Not applicable

Null - Not Null - Or?

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

Not applicable

Null - Not Null - Or?

did you try:

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

Not applicable

Null - Not Null - Or?

Yes, I tried that earlier and to no avail.

Not applicable

Re: Null - Not Null - Or?

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

johnpaul
Contributor

Re: Null - Not Null - Or?

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?

Not applicable

Null - Not Null - Or?

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
Contributor III

Null - Not Null - Or?

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
Valued Contributor

Null - Not Null - Or?

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

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