Autogenerating table values based on null values

[#1]

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

[d_pranskus · Accepted solution]

Hi

When you load your data you can use following aproach:

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

Cheers

Darius

[#3]

Simple but very effective!

Thanks

[johnw]

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.

[#5]

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!

[johnw]

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))
...