topic Re: How do I pull in all rows except for ones with a null value? in QlikView

How do I pull in all rows except for ones with a null value?

Wed, 25 Nov 2020 16:16:04 GMT

Maybe this is best suited for a SQL discussion but here goes.

I want to pull in all rows except for the ones where DOINVSTATUSID is not equal to NULL.

I tried WHERE DOINVSTATUSID <> ';'. ITMSNumber NOT LIKE '_%'; (because I don't want the ones with a character at the front), DOINVSTATUSID IS NOT NULL;

None of them work.

Re: How do I pull in all rows except for ones with a null value?

adamdavi3s — Thu, 01 Dec 2016 11:54:47 GMT

WHERE (DOINVSTATUSID IS NOT NULL OR DOINVSTATUSID NOT LIKE ';%')

Should work fine?

However you might find it isn't actually null but blank?

In which Case

WHERE (LEN(DOINVSTATUSID) >0 OR DOINVSTATUSID NOT LIKE ';%')

Re: How do I pull in all rows except for ones with a null value?

Thu, 01 Dec 2016 11:57:56 GMT

Thank you Adam!

Re: How do I pull in all rows except for ones with a null value?

kaushiknsolanki — Thu, 01 Dec 2016 11:58:10 GMT

Try below script,

I havent tested, but I guess this should work.

Sql Select * from Xyz where not isnull XYz;

Regards,

Kaushik Solanki

Re: How do I pull in all rows except for ones with a null value?

adamdavi3s — Thu, 01 Dec 2016 11:59:18 GMT

No worries which was the correct one in the end?? purely for my own interest!

Re: How do I pull in all rows except for ones with a null value?

Thu, 01 Dec 2016 12:06:33 GMT

WHERE (DOINVSTATUSID IS NOT NULL OR DOINVSTATUSID NOT LIKE ';%')

Re: How do I pull in all rows except for ones with a null value?

Thu, 01 Dec 2016 12:33:56 GMT

Adam it turns out that that wasn't right...

Re: How do I pull in all rows except for ones with a null value?

adamdavi3s — Thu, 01 Dec 2016 12:36:28 GMT

Not a problem, we are here to help!

Can you look at the data in SQL Management studio and if so what does it look like?

Re: How do I pull in all rows except for ones with a null value?

Thu, 01 Dec 2016 12:38:40 GMT

Re: How do I pull in all rows except for ones with a null value?

Anil_Babu_Samineni — Thu, 01 Dec 2016 12:40:53 GMT

Try like this

Load

FROM

<Table-Name>

Where

Len(DOINVSTATUSID)>0

not WildMatch(DOINVSTATUSID,'*;**');

Re: How do I pull in all rows except for ones with a null value?

adamdavi3s — Thu, 01 Dec 2016 12:41:56 GMT

So its correct in SSMS but Qlik is pulling in invalid rows?!

Re: How do I pull in all rows except for ones with a null value?

Thu, 01 Dec 2016 12:43:26 GMT

You're probably right Adam... I think there is something else going on here. I'll have to start a new discussion.

Re: How do I pull in all rows except for ones with a null value?

adamdavi3s — Thu, 01 Dec 2016 12:46:31 GMT

No worries, feel free to tag me in it and I will take a look

Re: How do I pull in all rows except for ones with a null value?

Thu, 01 Dec 2016 13:05:12 GMT

Hi plz check the attached doc, i hope it will help you

Re: How do I pull in all rows except for ones with a null value?

Thu, 01 Dec 2016 13:08:01 GMT

I can't open it.... I have personal edition I believe, even though I'm working for a conglomerate.