topic Re: Need to find fields which contains NULL values in QlikView

Need to find fields which contains NULL values

Thu, 29 Aug 2013 03:17:32 GMT

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

Re: Need to find fields which contains NULL values

uttamdwibedy — Thu, 29 Aug 2013 03:45:32 GMT

Hi,

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

rgds,

UKD

Re: Need to find fields which contains NULL values

jagan — Thu, 29 Aug 2013 03:57:44 GMT

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.

Re: Need to find fields which contains NULL values

Thu, 29 Aug 2013 04:01:41 GMT

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

Re: Need to find fields which contains NULL values

Thu, 29 Aug 2013 04:05:50 GMT

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

(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

Re: Need to find fields which contains NULL values

jagan — Thu, 29 Aug 2013 04:33:46 GMT

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.

Re: Need to find fields which contains NULL values

Thu, 29 Aug 2013 05:47:26 GMT

Hi Jagan,

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

Please do me the needful.

thnx

Re: Need to find fields which contains NULL values

hic — Thu, 29 Aug 2013 05:50:16 GMT

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

Re: Need to find fields which contains NULL values

Thu, 29 Aug 2013 06:00:37 GMT

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

Re: Need to find fields which contains NULL values

jagan — Thu, 29 Aug 2013 06:02:20 GMT

Hi,

Try like this

SET

NULLINTERPRET

null;

Data:

LOAD

INLINE

[

Temp:

LOAD

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

NullField

RESIDENT

Data;

Concatenate

(Temp)

LOAD

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

NullField

RESIDENT

Data;

Concatenate

(Temp)

LOAD

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

NullField

RESIDENT

Data;

Hope this helps you.

Regards,

jagan.

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

Thu, 29 Aug 2013 06:31:33 GMT

But jagan i cant do this for 247 fields right

please find the attached sample data.

in that sample data my desg and ph. no is null so i need find the column name and need to list it as a field.

it will chage day by day tommorow it may be my emp. name and ph.no

thnx

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

kaushiknsolanki — Thu, 29 Aug 2013 07:05:17 GMT

Hi,

Have a look at the attached example.

Hope this will clear your idea.

Regards,

Kaushik Solanki

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

Thu, 29 Aug 2013 08:26:52 GMT

hi Kaushik,

Yes.. this is what i want . thanks