Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

fgiorgio
Contributor

Resident Load where a field is Not Null

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:

product lists table.jpg

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Resident Load where a field is Not Null

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

15 Replies

Re: Resident Load where a field is Not Null

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
Valued Contributor

Re: Resident Load where a field is Not Null

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
Contributor III

Re: Resident Load where a field is Not Null

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)

fgiorgio
Contributor

Re: Resident Load where a field is Not Null

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

fgiorgio
Contributor

Re: Resident Load where a field is Not Null

Hmmm..

tried your suggestion but the table still does not load,

output of the textbox with expression:

=NullCount(ISHRDebName)

is 1...

fgiorgio
Contributor

Re: Resident Load where a field is Not Null

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

MVP
MVP

Re: Resident Load where a field is Not Null

>> 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
Valued Contributor

Re: Resident Load where a field is Not Null

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

fgiorgio
Contributor

Re: Resident Load where a field is Not Null

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

Community Browser