Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Have a look at the attached example.

Hope this will clear your idea.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

12 Replies
uttamdwibedy
Contributor
Contributor

Hi,

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

rgds,

UKD

jagan
Luminary Alumni
Luminary Alumni

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
Author

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
Author

  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

jagan
Luminary Alumni
Luminary Alumni

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
Author

Hi Jagan,

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

Please do me the needful.

thnx

hic
Former Employee
Former Employee

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
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.