topic Re: Select null values in set analysis in QlikView

Select null values in set analysis

Mon, 16 Dec 2013 15:53:57 GMT

What is the syntax to select null values in set analysis ?

{"=null()"} is not good...

Thanks

Re: Select null values in set analysis

Anonymous — Mon, 16 Dec 2013 15:55:40 GMT

Try this

YourField-={*}

Re: Select null values in set analysis

Gysbert_Wassenaar — Mon, 16 Dec 2013 15:55:51 GMT

Something like {<FieldX -= {'*'} >} usually works. But a better way is to replace nulls with real values in the script. See this document for more information: NULL handling in QlikView

Re: Select null values in set analysis

Mon, 16 Dec 2013 16:13:43 GMT

Thank You but

=count({$<LIAISON_POLICE_DTAFFIL={"<=$(=Effectifs_au)"},LIAISON_POLICE_DTRADIAT={">=$(=Effectifs_au)"}+{"01/01/0001"},ADHERENT_DTSORTIE={">=$(=Effectifs_au)"}+{<ADHERENT_DTSORTIE -= {'*'} >}>}ADHERENT_NOUNIQUE)

it returns nothing...

Re: Select null values in set analysis

Gysbert_Wassenaar — Mon, 16 Dec 2013 16:30:54 GMT

Yes, well you have some extra >} thingies. I'm not sure what you're trying to do, but the syntax isn't correct. Maybe this works:

=count({$<LIAISON_POLICE_DTAFFIL={"<=$(=Effectifs_au)"},LIAISON_POLICE_DTRADIAT={">=$(=Effectifs_au)"}+{"01/01/0001"},ADHERENT_DTSORTIE={">=$(=Effectifs_au)"}>}+{<ADHERENT_DTSORTIE -= {'*'} >}ADHERENT_NOUNIQUE)

Re: Select null values in set analysis

Tue, 17 Dec 2013 08:13:15 GMT

Hellow,

sorry but it don't works...

Does the field ADHERENT_DTSORTIE is null ?

Re: Select null values in set analysis

Tue, 17 Dec 2013 08:28:42 GMT

Hello Canevet,

Can you attach a sample file. or exact expression which you are using for ADHERENT_DTSORTIE.?

Thanks,

Angad

Re: Re: Select null values in set analysis

Tue, 17 Dec 2013 08:36:37 GMT

Here is an extraction xls of my table

Re: Select null values in set analysis

Tue, 17 Dec 2013 08:41:00 GMT

By sample file i meant, the sample of QVW file. so that I can check the expressions.

Thanks,

Angad

Re: Select null values in set analysis

Tue, 17 Dec 2013 08:56:23 GMT

ADHERENT:

LOAD ADHERENT_NOASSUR,

ADHERENT_NOBENEF,

PREVOYANCE_LIEN_NOASSUR,

ADHERENT_NOUNIQUE,

ADHERENT_NOSS,

ADHERENT_CDBENEF,

ADHERENT_LIBELLE_BENEF,

ADHERENT_CDCIVI,

ADHERENT_CODEVIP,

ADHERENT_DTNAIS,

ADHERENT_DTENTREE,

ADHERENT_DTSORTIE,

ADHERENT_MOTIF_SORTIE,

ADHERENT_LIB_RGLT,

ADHERENT_CDATTACH,

ADHERENT_NOATTACH,

ADHERENT_NMTIERS,

ADHERENT_NM2TIERS,

ADHERENT_CDPOST,

ADHERENT_NMVILLE

FROM

$(Chemin_qvd)ADHERENT.qvd

(qvd);

Re: Select null values in set analysis

Tue, 17 Dec 2013 10:08:09 GMT

if (Date(DTSORTIE)=-693593, Null(), date(DTSORTIE)) as ADHERENT_DTSORTIE,

Re: Select null values in set analysis

Mon, 13 Jan 2014 14:09:54 GMT

Hi,

From my experience: best is to replace nulls with something else on loading (like date(makedate(2099,12,31)) ); if it's not possible:

{<FieldX -= {'*'} >} doesn't work for me; {$-<FieldX = {'*'} >} works but can be tricky to handle with a complex filter; working in all cases, classical Count(If( with Isnull() function. I go for the last solution...

Hope it can help.