topic Recognizing null values in a Table File in QlikView

Recognizing null values in a Table File

Thu, 30 Jul 2009 22:46:09 GMT

Hi again,

I'm working with data that contains a set of Orders. Each Order has an Order ID, and a single Screen Status. What I want to do is then assign another value to different screen statuses called Advice Status. This is simple enough, and i've done it in an Excel file; two columns, screen status and advice status, with each relation mapped out.

This works well, however, there are some orders where the screen status is null. For this case I still want to assign the order with an Advice status of "Not Validated." I tried doing this with a row in the excel file that has a blank Screen Status, but qlikview won't import that row. Does anyone know how I can do this assignment? I was thinking if statements in the load to assign a value to orders with a null Screen Status, but I'm not sure how to do this.

Thanks,

Dan

Recognizing null values in a Table File

Thu, 30 Jul 2009 22:58:46 GMT

If i'm reading this correctly you just want to assign a value to a null field.

if(isnull([FIELDNAME]) = -1), 'This field is null', FIELDNAME) as FIELDNAME

Recognizing null values in a Table File

Thu, 30 Jul 2009 23:31:50 GMT

Rather than test for null as there are differences between the way 32bit and 64bit recognise it, its safer to do a length test like;

if(len([FIELDNAME]) = 0), 'This field is null', FIELDNAME) as FIELDNAME

if you also want to include a space filled column then use:

if(len(trim([FIELDNAME])) = 0), 'This field is null or spaces', FIELDNAME) as FIELDNAME

Regards,

Gordon

Recognizing null values in a Table File

Fri, 31 Jul 2009 00:42:20 GMT

my current syntax is this:


LOAD [Screen Status] as [Original Screen Status],
 [Advice Status] as [Original Advice Status]
FROM Screens.xls (biff, embedded labels, table is [Screen Advice Status$]);

where does that if statement fit into this? What I want it to represent is, if the Screen Status is null, then assign a value of 'Not Validated' to Original Advice Status, otherwise give it Advice Status. Here is what I've edited in, but it still doesn't seem to work, the orders that have a null screen status are still displaying a null advice status


LOAD [Screen Status] as [Original Screen Status],
 if(len(trim([Screen Status])) = 0, 'Not Validated', [Advice Status]) as [Original Advice Status]
FROM Screens.xls (biff, embedded labels, table is [Screen Advice Status$]);

Recognizing null values in a Table File

Oleg_Troyansky — Fri, 31 Jul 2009 01:26:01 GMT

Dan,

you need something similar in your Orders load - replace null (or missing) screen status for the Order with the text "Not Validated" or someting like that...

Oleg

Recognizing null values in a Table File

Fri, 31 Jul 2009 01:33:04 GMT

ah ic, that's a good idea. I'll give a value to null screen numbers, and then give orders with the null screen number a screen status of 'Not Validated' based on that value.

Thanks guys!