Resident Load where a field is Not Null

[Anonymous]

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

[Anil_Babu_Samineni]

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;

[luismadriz]

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

[woshua5550]

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)

[Anonymous]

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...

[Anonymous]

Hmmm..

tried your suggestion but the table still does not load,

output of the textbox with expression:

=NullCount(ISHRDebName)

is 1...

[Anonymous]

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...

[jonathandienst · Accepted solution]

>> 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;

[luismadriz]

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

[Anonymous]

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