Need to find fields which contains NULL values

[#1]

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

[uttamdwibedy]

Hi,

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

rgds,

UKD

[jagan]

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.

[#4]

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

[#5]

  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]

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.

[#7]

Hi Jagan,

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

Please do me the needful.

thnx

[hic]

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

[#9]

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]

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.