Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
mhmmd_srf
Not applicable

Null Handling in Data Loading Script

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

1 Solution

Accepted Solutions
jyothish8807
Not applicable

Re: Null Handling in Data Loading Script

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

Best Regards,
KC
6 Replies
jyothish8807
Not applicable

Re: Null Handling in Data Loading Script

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

Best Regards,
KC
antoniotiman
Not applicable

Re: Null Handling in Data Loading Script

Hi,

Try

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

Regards,

Antonio

its_anandrjs
Not applicable

Re: Null Handling in Data Loading Script

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
Not applicable

Re: Null Handling in Data Loading Script

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

of

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

vineetadahiya12
Not applicable

Re: Null Handling in Data Loading Script

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
Not applicable

Re: Null Handling in Data Loading Script

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