topic Re: Resident Load where a field is Not Null in QlikView

Resident Load where a field is Not Null

Anonymous — Tue, 14 Nov 2017 23:37:35 GMT

Hi all,

This should be a simple statement but i cannot get it to work for me...

I have a table loaded from my Database via ODBC...

I need to clean up a table by only loading rows where the field "ISHRDebName" is not NULL

Here's my script:

ISHRBProductLists:

Load

ISHRBSKU,

ISHRBListMastID,

ISHRBListDescription,

ISHRBDebAcc,

ISHRBDebName

Resident ISHRBProductLists_temp WHERE Len(Trim(ISHRBDebName))>0;

I have tried: (last line)

Resident ISHRBProductLists_temp WHERE Len(ISHRBDebName)>0;

I have also tried:

Resident ISHRBProductLists_temp WHERE NOT ISNull(ISHRBDebName);

I have also tried:

Resident ISHRBProductLists_temp WHERE ISNull(ISHRBDebName)=0;

here's the table I'm loading from:

I need to omit rows where ISHRBDebName is Null ("-").

Alternatively, I could only load Rows where ISHRBDebAcc is a number between 1 and 999999...

can anyone advise on this?

Thanks...

Fabrizio Giorgio

Message was edited by: Fabrizio Giorgio

Re: Resident Load where a field is Not Null

Anil_Babu_Samineni — Wed, 15 Nov 2017 01:34:19 GMT

I need to omit rows where ISHRBDebName is Not Null ("-").

For this, You could use WHERE Len(Trim(ISHRBDebName))<0;

Alternatively, I could only load Rows where ISHRBDebAcc is a number between 1 and 999999...

For this, WHERE ISHRBDebAcc >= 1 and ISHRBDebAcc <= 999999;

Re: Resident Load where a field is Not Null

luismadriz — Wed, 15 Nov 2017 02:09:24 GMT

Hi Fabrizio,

I think your Where clause Len(Trim(ISHRBDebName))>0 should've worked if you wanted to remove Nulls. Maybe that field could have other invisible stuff in it as pointed in this paper: NULL handling in QlikView

Please try this: Len( Purgechar( ISHRBDebName, Chr(09) & Chr(32) & Chr(160) & Chr(12288)) ) > 0

Testing ISHRBDebAcc seems simpler but I guess you want to understand what's up with those Nulls...

I hope this is helpful,

Cheers,

Luis

Re: Resident Load where a field is Not Null

woshua5550 — Wed, 15 Nov 2017 04:01:11 GMT

in my view, where clause should be like this

Resident ISHRBProductLists_temp Where ISHRBDebName <> Null() ;

After you load, what is the result of this expression in text object ?

=NullCount(ISHRDebName)

Re: Resident Load where a field is Not Null

Anonymous — Wed, 15 Nov 2017 05:17:39 GMT

Hi Anil,

thanks for your reply...

my apologies, I meant:

I need to omit rows where ISHRBDebName is Null ("-").

however both of your suggestons unfortunately did not work and the table still does not load at all...

Re: Resident Load where a field is Not Null

Anonymous — Wed, 15 Nov 2017 05:20:06 GMT

Hmmm..

tried your suggestion but the table still does not load,

output of the textbox with expression:

=NullCount(ISHRDebName)

is 1...

Re: Resident Load where a field is Not Null

Anonymous — Wed, 15 Nov 2017 05:23:02 GMT

Hi Luis,

both approaches are not working for me as yet...

I also tried Len( Purgechar( ISHRBDebName, Chr(09) & Chr(32) & Chr(160) & Chr(12288)) ) > 0

And the table still does not load....

should that have worked regardless whether ISHRBDebName was NULL or Nothing?

I'm at a bit of a loss...

Re: Resident Load where a field is Not Null

jonathandienst — Wed, 15 Nov 2017 05:24:51 GMT

>> the table still does not load

Perhaps its auto-concatenating to the source table (if the field names are the same). Try

ISHRBProductLists:

Noconcatenate

Load

ISHRBSKU,

...

Resident ISHRBProductLists_temp

WHERE Len(Trim(ISHRBDebName))>0;

DROP Table ISHRBProductLists_temp;

Re: Resident Load where a field is Not Null

luismadriz — Wed, 15 Nov 2017 05:34:46 GMT

Hi F,

I never knew that your table wasn't loading at all, I thought the issue was that table was being loaded with Nulls. If the table is not loading then the issue is completely different.

Please try the NoConcatenate option that Jonathan just described,

Good luck,

Cheers,

Luis

Re: Resident Load where a field is Not Null

Anonymous — Wed, 15 Nov 2017 05:41:41 GMT

you are a genius!!

yes i was dropping the source table,

and your suggestion to NoConcatenate worked!!

Not sure why it was concatenating in the first place... but i shall watch out for that one if i have a table that sort of "goes missing"

I do use concatenation quite a bit in this script, so it might have been an oversight earlier in the script...

in the end I settled for:

Resident ISHRBProductLists_temp Where ISHRBDebName <> Null();

because it's a bit less technical incase someone looks at this in the years to come...

Thank you so much for your contribution!! I would never have picked that!!!

Cheers, fab

Re: Resident Load where a field is Not Null

Anonymous — Wed, 15 Nov 2017 05:45:41 GMT

You are right Luis,

i realise i was a bit ambiguous now...

thank you for your contribution though, it all helps to get to the right answer!!

Apreciate you taking the time!!

Cheers,

Fab

Re: Resident Load where a field is Not Null

jonathandienst — Wed, 15 Nov 2017 05:58:44 GMT

>>Where ISHRBDebName <> Null();

That wont work properly. Like in SQL, a comparison with NulI() will always be false. Your original condition was correct and the best option.

Re: Resident Load where a field is Not Null

luismadriz — Wed, 15 Nov 2017 06:03:09 GMT

HI,

I don't think you were dropping the table, I think it was being concatenated to the previous one... well I'm not sure of those rules, maybe Jonathan can explain a bit more?

Cheers,

Re: Resident Load where a field is Not Null

Anil_Babu_Samineni — Wed, 15 Nov 2017 06:06:26 GMT

Okay

Re: Resident Load where a field is Not Null

Anonymous — Wed, 15 Nov 2017 21:59:54 GMT

Hi Luis,

yes it must have been concatenating to the previous (source) table which I was then dropping in the next line...

hence why I wasn't seeing the table at all...

I'm not entirely sure why it was concatenating, but the NoConcatenate statement definitely fixed it...

Thanks again...

Re: Resident Load where a field is Not Null

Anonymous — Wed, 15 Nov 2017 22:21:19 GMT

Hi Jonathan,

thanks for that input, and noted...

<Where ISHRBDebName <> Null();> did give me the correct result (i.e. omitted rows where ISHRBDebName was NULL)

However I have reverted back to <WHERE Len(Trim(ISHRBDebName))>0; > based on your recommendation…

Thanks for your help and advice…

Appreciate you taking the time…

Cheers…

Fab