topic isNull() does not Work in QlikView

isNull() does not Work

Mon, 12 Jul 2010 14:57:53 GMT

I have a list of receptnumbers and from this list I select the changes in the Recept with this Formula


if(isnull(Recipe), null(),if(Recipe = previous(Recipe), 0, 1)) as RecipeChange.
[/ncode]
I need a expression which gives me the following.
1) If I select month and date, I need the first Number to be always 1.
2) Another problem is that all the Numbers are not filled in the Databank. If there are no Numbers it must still consider it as a change i.e value must be 1
For ex.. In the Recept Number Field The first one is 1 and after that if the number is equal to previous Number the change is 0 and if it is not equal then 1. Even though there is a blank or nothing it must take as a seperate Change. The First in the list is automatically 1. I hope I am clear.
 <table><col></col> <col></col> <tbody><tr><td>Recept Numbers</td><td> Recept Change</td></tr><tr><td>12345</td><td>1</td></tr><tr><td>12345</td><td>0</td></tr><tr><td>-</td><td>1</td></tr><tr><td>12345</td><td>1</td></tr><tr><td>12346</td><td>1</td></tr><tr><td>12346</td><td>0</td></tr></tbody></table>

3) If the Number changes from null() to RezeptNumber this formula works but when It changes from Number to Null(). This does not work.

Ideas are appreciated. Thanks in Advance
Sravan
 </body>

isNull() does not Work

Mon, 12 Jul 2010 16:08:13 GMT

Any one..can you help me..

isNull() does not Work

Mon, 12 Jul 2010 16:09:35 GMT

Maybe it is not null. Maybe it's 0 or a blank string like this ''. Try to check a length of the value.

isNull() does not Work

Mon, 12 Jul 2010 16:13:23 GMT

Hi Nick ,

If it is a blank like you said. How can I change the code so that It may give me correct result.

Thanks for your answer.

isNull() does not Work

Mon, 12 Jul 2010 17:01:05 GMT

There are issues with 64bit and null so always use len() to test for null for portability:

if (len([field name]) = 0, x, y)

Regards,

Gordon

isNull() does not Work

Mon, 12 Jul 2010 17:30:36 GMT

hello Gordon,

Thanks.. so what can the formula be..

if(len(Recipe) = 0, 'None', if(Recipe = Previous(Recipe), 0 ,1) as Recipe change. This does not work. Why I dont get None in my Recipe, I dont understand

isNull() does not Work

Mon, 12 Jul 2010 17:46:46 GMT

Then your data is not null. Does it just contain spaces (spaces are as much a character as 'a' or anything else dont forget)? If you want to count a field full of spaces as empty then use this where trim removes both leading and trailing spaces:

if(len(trim(Recipe)) = 0, .....

Regards,

Gordon