Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Need to find fields which contains NULL values

hi all,

in this below script my DESG and PH.NO fields contains null values. but i cant get that fields name by using the below IF condition. Please help me on this .

test:
LOAD [Emp No],
     [Emp Name],
     Desg,
     Address,
     Ph.No,
     If(IsNull([Emp No]),'[Emp No]' , If(IsNull([Emp Name]),'[Emp Name]' , If(isnull(Address),'Address' , If(isnull(Ph.No),'Ph.No' , If(isnull(Desg),'Desg'))))) as NullFields
FROM
[alert testing data.xlsx]
(ooxml, embedded labels, table is Sheet1);

thnx

1 Solution

Accepted Solutions

Re: Re: Re: Need to find fields which contains NULL values

Hi,

Have a look at the attached example.

Hope this will clear your idea.

Regards,

Kaushik Solanki

12 Replies
uttamdwibedy
New Contributor

Re: Need to find fields which contains NULL values

Hi,

try with If(IsNull(Desg),[Emp no]) or If(IsNull(Phno.),[Emp No]) as Nullfields

rgds,

UKD

MVP
MVP

Re: Need to find fields which contains NULL values

Hi,

Try like this

SET NULLINTERPRET=null;   // specify your null value here, if it is NULL then specify NULL

LOAD F1,
F2,
If(IsNull(F1), 'F1', If(IsNull(F2), 'F2', 'N/A')) AS NullField
INLINE [
    F1, F2
    null,1
    1, null
    2, 5  
];

Regards,

Jagan.

Not applicable

Re: Need to find fields which contains NULL values

Hi UKD,

I tried this

 

If(IsNull(Desg),'Desg') or If(IsNull([Emp No]),'[Emp No]') or
If(IsNull([Emp Name]),'[Emp Name]') or If(IsNull(Address),'Address') or
If(IsNull(Ph.No),'Ph.No')
as Nullfields

but no its not working.

thnx

Not applicable

Re: Need to find fields which contains NULL values

  hi ,

****  ALL THE FIELD VALUES SHOULD BE NULL THEN ONLY I NEED TO FIND THAT FIELD. I DONT WANT THE FIELDS NAMES WHICH CONTAINS ONE OR TWO NULL VALUES

SET NULLINTERPRET=null;
test:

LOAD

[Emp No]

,
[Emp Name]

,
Desg

,
Address

,
Ph.No

,

If

(IsNull

([Emp No]

),'[Emp No]' ,
If

(IsNull

([Emp Name]

),'[Emp Name]' ,
If

(isnull

(Address

),'Address' ,
If

(isnull

(Ph.No

),'Ph.No' ,
If

(isnull

(Desg

),'Desg', 1))))) as

NullFields

FROM
[alert testing data.xlsx]
(

ooxml,

embedded

labels,

table

is Sheet1);
i tried this but still its not working

MVP
MVP

Re: Need to find fields which contains NULL values

Hi,

Try like this, use MaxString() for each field if you get a value then it is not a null field.  You should not do this in Load script, you should do this separately in variables.

Or else simply list all the fields in list boxes then if you got any value it is not a null field.

Regards,

jagan.

Not applicable

Re: Need to find fields which contains NULL values

Hi Jagan,

But i need that as a field. so i have to load it from backend

Please do me the needful.

thnx

Employee
Employee

Re: Need to find fields which contains NULL values

Are you sure that the empty values really are NULL? They could be empty strings also.

I would not use IsNull(Field) as test. I would use Len(Trim(Field))=0 instead.

See more on NULL handling in QlikView.

HIC

Not applicable

Re: Need to find fields which contains NULL values

Hi Henric,

  i used the below code as per ur comment

If(len(trim([Emp No]))=0,'[Emp No]' )or
If(len(trim([Emp Name]))=0,'[Emp Name]' ) or
If(len(trim(Desg))=0,'Desg' ) or
If(len(trim(Address))=0,'Address' ) or
If(len(trim(Ph.No))=0,'Ph.No') as test,

but still same issue. Please help me to solve this.

my requiremnt is I NEED TO FIND THE COLUMN-NAMES FROM THE TABLE WHICH DONT HAVE VALUES. IT MAY MAY BE null or - or ' '.

hope you understand my difficulties

MVP
MVP

Re: Need to find fields which contains NULL values

Hi,

Try like this

 

SET

NULLINTERPRET

=

null;

 

Data:

 

LOAD

F1

, 

F2

,

 

F3

 

INLINE

[

 

Temp:

 

LOAD

 

If

(IsNull(MaxString(F1)), 'F1') AS

NullField

 

RESIDENT

Data;

 

Concatenate

(Temp) 

LOAD

 

If

(IsNull(MaxString(F2)), 'F2') AS

NullField

 

RESIDENT

Data;

 

Concatenate

(Temp) 

LOAD

 

If

(IsNull(MaxString(F3)), 'F3') AS

NullField

 

RESIDENT

Data;

Hope this helps you.

Regards,

jagan.

Community Browser