topic Re: Transform a value field into Null in QlikView

Transform a value field into Null

Tue, 22 Dec 2015 17:07:56 GMT

Hello,

I am working in a project in which some of the data source comes from CSV. When I load those data, there is some fields with strings values, written as 'NULL'. In QV this will not really count as null.

Is there way for me to convert those values to a proper NULL?

Is this condition script enough to achieve the goal?

If (FieldName= 'NULL', ' ', FieldName)

Thank you in advance!

Iuri

Re: Transform a value field into Null

sunny_talwar — Tue, 22 Dec 2015 17:10:16 GMT

Above should work after a small change:

If(FieldName = 'Null', Null(), FieldName) as FieldName

Re: Transform a value field into Null

Tue, 22 Dec 2015 17:17:36 GMT

Thank you for your help Sunny.

Re: Transform a value field into Null

sunny_talwar — Tue, 22 Dec 2015 17:18:26 GMT

Not a problem

Re: Transform a value field into Null

Wed, 23 Dec 2015 14:03:30 GMT

Hi Sunny,

I just remembered something...

I have some tables with fields with 'NULL' (as string).

Is it possible to transform it into proper null automatically?

For example... if I declare a variable with the NUll()...

Is it possible to achieve it? Have you ever done that before?

Thanks in advance for your help.

Regards,

Iuri

Re: Transform a value field into Null

sunny_talwar — Wed, 23 Dec 2015 14:17:30 GMT

I have tried looking around, but havn't found a function which would replace all instances of 'NULL' (string) into actual NULL. I will continue looking, but right now, I am not sure if I have an answer for you

Re: Transform a value field into Null

Wed, 23 Dec 2015 14:27:00 GMT

Thank you very much for your effort.

To make it automatic replaced is not an urgent thing, I asked more for curiosity.

Re: Transform a value field into Null

sunny_talwar — Wed, 23 Dec 2015 14:29:47 GMT

May be we can seek help from experts on this topic -> hic‌, rwunderlich‌, swuehl‌, MRKachhiaIMP‌, jagan‌, MarcoWedel‌, bill.markham‌

Re: Transform a value field into Null

MK_QSL — Wed, 23 Dec 2015 14:38:08 GMT

Check this video.. .not exactly the same you are looking for but may be useful..

Convert Null values using NullAsValue statement in QlikView - YouTube

Re: Transform a value field into Null

sunny_talwar — Wed, 23 Dec 2015 14:41:59 GMT

Manish this video seems to be the opposite of what is needed.

NullAsValue would change Null() -> 'NULL' (for example)

but is it possible to go the other way?

'NULL' -> Null()?

Re: Transform a value field into Null

MK_QSL — Wed, 23 Dec 2015 14:44:27 GMT

'NULL' to Null() Suggested by you is the best till date I have used..

Re: Transform a value field into Null

MK_QSL — Wed, 23 Dec 2015 14:45:19 GMT

Re: Transform a value field into Null

MarcoWedel — Thu, 24 Dec 2015 01:31:58 GMT

test data

Re: Transform a value field into Null

MarcoWedel — Thu, 24 Dec 2015 01:35:19 GMT

Hi,

I might be missing something here, but I think 'NullInterpret' does exactly what you are looking for.

It defines a string that will be interpreted as 'real' NULL when loaded.

So a solution might be:

SET NullInterpret='NULL';

table1:
LOAD ID,
    FieldName
FROM [https://community.qlik.com/servlet/JiveServlet/download/940087-203416/QlikCommunity_Thread_197487.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

hope this helps

regards

Marco

Re: Transform a value field into Null

sunny_talwar — Thu, 24 Dec 2015 02:34:50 GMT

I tried it with an Inline table and it did not work. I will try to see if I still have that sample