Selecting NULL-Values

[#1]

Hello everybody

I have some Problems with a SetAnalysis Expression.

I try to select values based on a field (Z_ZahlungsCode) that only contains two values. These values are NULL or the character "*".

If I use this expression:

Sum ({$<[R_Debitor_abgschlossen]={"YES"}, [Z_ZahlungsCode]={"*"}>}[Z_ZahlungsBetrag])

I get the the correct results (the sum of all records where Z_Zahlungscode is *)

But actually I want it the other way around. I would like to have the sum of all records with the NULL-Value. I tried different things like

Sum ({$<[R_Debitor_abgschlossen]={"YES"}, [Z_ZahlungsCode]={""}>}[Z_ZahlungsBetrag])

Sum ({$<[R_Debitor_abgschlossen]={"YES"}, [Z_ZahlungsCode]=NULL>}[Z_ZahlungsBetrag])

Sum ({$<[R_Debitor_abgschlossen]={"YES"}, [Z_ZahlungsCode]-={"*"}>}[Z_ZahlungsBetrag])

Sum ({$<[R_Debitor_abgschlossen]={"YES"}, [Z_ZahlungsCode]=0>}[Z_ZahlungsBetrag])

But all these variations did not work. The result was allways Sum = 0

Does anybody have an idea?

Thanks a lot

Achim

[sunny_talwar]

May be this:

Sum({$<[R_Debitor_abgschlossen]={"YES"}, [Z_ZahlungsCode]={"=NullCount([Z_ZahlungsCode]) > 0"}>}[Z_ZahlungsBetrag])

or

Sum({$<[R_Debitor_abgschlossen]={"YES"}, [Z_ZahlungsCode]={"=Len(Trim([Z_ZahlungsCode])) = 0"}>}[Z_ZahlungsBetrag])

or

Sum({$<[R_Debitor_abgschlossen]={"YES"}, [Z_ZahlungsCode]={"=IsNull([Z_ZahlungsCode])"}>}[Z_ZahlungsBetrag])

[swuehl]

I would suggest using something like

Sum ({$<[R_Debitor_abgschlossen]={"YES"}, KeyField = e({< [Z_ZahlungsCode]={"*"}>}) >} [Z_ZahlungsBetrag])

See also

Excluding values in Set Analysis

[#4]

Hello Sunny

Thank you for your help. Unfortunatly this does not work.

Maybe it is an issue of the database and the datamodel that I use. As you can see below. I have two records for each bill (bill number in first colum, highlighted in green). One record stands for the sum on the bill (highlighted in yellow) indicated bei the *(highlighted in pink). I can calculate a sum on these records with the * (see Chart, red bar). I also opend a list box to filter the field "Z_Zahlungscode". But if you can see, there ist only the * that I can select. In the table below the "NULL"-Values seem to be indicated bei a "-".

Maybe you have another idea.

Achim

[#5]

Hello

Thank you for your help. Unfortunatly this does not work.

Maybe it is an issue of the database and the datamodel that I use. As you can see below. I have two records for each bill (bill number in first colum, highlighted in green). One record stands for the sum on the bill (highlighted in yellow) indicated bei the *(highlighted in pink). I can calculate a sum on these records with the * (see Chart, red bar). I also opend a list box to filter the field "Z_Zahlungscode". But if you can see, there ist only the * that I can select. In the table below the "NULL"-Values seem to be indicated bei a "-".

Maybe you have another idea.

Achim

[swuehl]

What have you used as KeyField? You need to use a KeyField that with unique values for the single lines in your table.