Null Handling in Data Loading Script

[mhmmd_srf]

Hi All,

I have a date field Created_On at source. From source to QVD we are loading as it. Few records are NULL or Empty in Created_On. We need to implement such a logic so that Null fields will be populated as "Empty". Like below:

if (Created_On = Null)

then ("Empty")

else

(Created_On)

Please suggest.

Thanks in advance for your help.

Sarif

[jyothish8807 · Accepted solution]

Hi Mohammad,

I have suggested a solution in your previous discussion.

A simply way is to make change in back end.

eg.

Load filed1,

if(len(trim(closed_at))=0,1,closed_at) as closed_at

from<table>.

Now in expression:

Count({$<closed_at = {'1'},Req_state={3}>}Req_number)

Regards

KC

[antoniotiman]

Hi,

Try

If(Len(Trim(Created_On)) = 0,'Empty',Cretaed_On) as Created_On

Regards,

Antonio

[its_anandrjs]

Hi,

You can create such a logic in the qlikview to find out first null values then put flag for that on the script.

Ex:-

Load

if( isnull(Created_On) = -1 or Len(Created_On) = 0 or Trim(Len(Created_On)), 'Empty', Created_On) as New_Created_On

From Location;

Regards,

Anand

[shraddha_g]

If(Len(Created_On) < 2,'Empty',Cretaed_On)

of

if(isnull(Created_On),'Null',Created_On)

[vineetadahiya12]

There are number of ways you can refer to.

1) You can refer the existing thread

How to replace Null() by a string value?

2) Solution that I usually use to handle NULLs  in script

Create a table

NullMap:

MAPPING LOAD

null(), 'Empty' AutoGenerate 1;

Where your actual logic script starts, write this as 1st statement:

MAP Created_On USING NullMap;

You can use * in place of Create_On if you want to fill all/any Null value. This will make Null selectable also.

[udit_kumar_sana]

Hi ,

You can try like this:-

Data:

load * inline

[

X,Y,Z

1,PQR,A

2,ABC,C

,SDF,V

5,VFR,G

];

Final_Data:

load

X,

Y,

Z,

'Empty' as Flag

Resident Data

where not Len(X)>0;

Final_Data:

load

X,

Y,

Z,

'Closed On' as Flag

Resident Data

where Len(X)>0;

drop table Data;

Regards,

Udit

[Ravi_Shankar_M]

Good Day! This is a good check, but you have a logic error which I have updated below:

if( isnull(Created_On) = -1 or Len(Created_On) = 0 or Trim(Len(Created_On)) = 0, 'Empty', Created_On) as New_Created_On

Thanks, Cheers!

Ravi Shankar M.