topic Autogenerating table values based on null values in QlikView

Autogenerating table values based on null values

Tue, 13 Apr 2010 11:48:10 GMT

I am producing a report on data generated from a CRM system that logs enquiries for an IT help desk.

An enquiry can be opened or closed; And therefore can have a "status" of 0 or 1. However a bug in the CRM system has allowed a few enquiries to have a "ClosedDate" as well as a "status" of 0 which indicates that they are opened. I cannot do anything about the bug! So to work around this problem I was considering Autogenerating a table with a single row for every enquiry in the main table, and if the "CLOSEDDATE" field is Null in that table, have the status in the AutoGenerated table set to open, otherwise have it set to closed.

In my head this makes sense and I think it should be a fairly straight forward task.

Unfortunately I have no idea how to go about this!

Can anyone point me to any tutorials or give me any tips on how to achieve this.

Thanks
Barry K

Autogenerating table values based on null values

d_pranskus — Tue, 13 Apr 2010 12:05:14 GMT

When you load your data you can use following aproach:

Load
IF(Isnull(ClosedDate), 0, Status) AS Status,
....

Cheers

Darius

Autogenerating table values based on null values

Wed, 14 Apr 2010 14:49:16 GMT

Simple but very effective!

Thanks

Autogenerating table values based on null values

johnw — Wed, 14 Apr 2010 23:10:36 GMT

The isnull() function is unreliable in some environments, I think 64-bit. I would replace it with len(Field)=0 for safety and better portability.

Autogenerating table values based on null values

Tue, 30 Nov 2010 23:11:51 GMT

Is there a "smart" way to perform this for all fields when you load? By smart, I mean without explicitly writing the statement for each individual fields? Thanks!

Autogenerating table values based on null values

johnw — Tue, 30 Nov 2010 23:23:10 GMT

I'm not thinking of a way. You're overriding values, which I think means you're going to need to explicitly code something. Now if you ALWAYS want to replace null with the same thing, like 'NULL', you could probably do something like this:

LET nullit = 'if(len($1),$1,' & chr(39) & 'NULL' & chr(39) & ') as $1';

LOAD
$(nullit(OrderID))
,$(nullit(OrderDate))
...