topic Re: Regarding Null in QlikView

Regarding Null

sarfaraz_sheikh — Tue, 18 Nov 2014 08:22:47 GMT

Hi ALL,

When i am writing the Or after IsNull(DD_TYPE) then it is fetching the rows....but when i am writing "and" after IsNull(DD_TYPE) nothing is getting fetched ....i dont know what happming exactly .....is i am doing wrong please suggest me right answer....

Below is my code ...i want all the null records in my report ....

IsNull(DD_TYPE) and

WildMatch(DD_TYPE,'EN','NB','RW','RO','UC')

Please reply its urgent ......

Sarfaraz

Re: Regarding Null

salto — Tue, 18 Nov 2014 08:25:19 GMT

Hello Sarfaraz,

Correct me if I am wrong but, if DD_TYPE is null, it will never match any value like 'EN', 'NB' os something similar, because it is a null value.

Hth.

Re: Regarding Null

MK_QSL — Tue, 18 Nov 2014 08:25:59 GMT

Use Match Instead of WildMatch

and

Instead of using IsNull Try to create a Flag for Null in Script

If(IsNull(DD_TYPE) or Len(Trim(Replace(DD_TYPE,'-','')))=0, 1, 0) as DDTypeNullFlag

......

Also, use

If(IsNull(DD_TYPE)) OR

If(WildMatch(DD_TYPE,'EN','NB','RW','RO','UC'))

Re: Regarding Null

vardhancse — Tue, 18 Nov 2014 08:30:19 GMT

The data in a field will be either 'Null' or a value. But can not be both.

what ever we use WildMatch or Match..

And so in AND condition both the conditions are not satisfied. so showing no rows.

only in OR condition we its possible to get some records.

Re: Regarding Null

hic — Tue, 18 Nov 2014 08:32:24 GMT

If you want the records where DD_TYPE is NULL, you should use

Where IsNull(DD_TYPE)

Where Len(Trim(DD_TYPE)) = 0

and no additional condition. (See Jose's answer above).

If you don't get the records you want, you can debug by removing this condition from the where-clause and put it in a field instead:

IsNull(DD_TYPE) as DebugField

HIC

Re: Regarding Null

michael_anthony — Tue, 18 Nov 2014 08:35:21 GMT

Is this in your script or a chart expression. Assuming script, what is in the source record. Eg. An empty string may not return Null. Also why are you testing IsNull and Wildmatch. What is in the source and what are you trying to achieve.

If a record was Null and so true, then wildmatch would find no match and return 0 which is interpreted as false. So can never satisfy the AND.

Re: Regarding Null

sarfaraz_sheikh — Tue, 18 Nov 2014 08:52:45 GMT

dear all,

I want both in my record null record as well as contains Value record what shoud i have to do ,,,please tell its urgent...

Sarfaraz

Re: Regarding Null

hic — Tue, 18 Nov 2014 09:03:35 GMT

If you want both records with NULL and records with an other value, you should use OR (what you initially suggested):

IsNull(DD_TYPE) or WildMatch(DD_TYPE,'EN','NB','RW','RO','UC')

HIC

Re: Regarding Null

PrashantSangle — Tue, 18 Nov 2014 09:12:03 GMT

Hi,

Can you explain your requirement with example?

It looks easy to solve.

Regards

Re: Regarding Null

sarfaraz_sheikh — Tue, 18 Nov 2014 09:26:06 GMT

You are write mr. henric.....but how i can identify that the particula recontans null value....because i am unable to find the null records in application......

Sarfaraz

Re: Regarding Null

sarfaraz_sheikh — Tue, 18 Nov 2014 09:28:33 GMT

my requirement is how i can recognise the null records in table ....

Sarfaraz

Re: Regarding Null

hic — Tue, 18 Nov 2014 09:30:48 GMT

Create a couple of Debug fields that will help you understand your data, e.g.

IsNull(DD_TYPE) as DebugField1,

Len(Trim(DD_TYPE)) as DebugField2,

and use the following where clause:

Len(Trim(DD_TYPE))=0 or WildMatch(DD_TYPE,'EN','NB','RW','RO','UC')

The "Len(Trim(DD_TYPE))=0" may be a better condition than IsNull(), since you could have records that are empty but still not NULL.

HIC

Re: Regarding Null

Anonymous — Tue, 18 Nov 2014 10:22:20 GMT

Hi,

At the script level,create one field in the table that satisfies your condition ,take a look at the following example:

*************************************************************************

data:

LOAD name,

DD_TYPE

FROM

data.xls

(biff, embedded labels, table is Sheet1$);

data1:

load name,DD_TYPE,

if(IsNull(DD_TYPE) or WildMatch(DD_TYPE,'EN','NB','RW','RO','UC'),1,0) as test

resident data;

drop table data;

************************************************************************

then in the front end ,create one chart having following expression

only({<test={1}>}name)

You will get what you are looking for but make sure field 'name' must not contain any null values .

Hope this will help you.

Thanks and Regards,

Geeta