8 Replies Latest reply: Jul 6, 2018 6:46 AM by Mindaugas Lekerauskas

# Remove null values at measures

Hi

I have created pivot table with one dimension iin column and one dimension in row and another measure.

I have null values for few records as below. we should exclude reocrds with null values at measure

report is like below

D1               D2                D3

Cus1            100               -                     -

Cus2              -                 20                   -

Cus3              10             -                         -

Cus4              -             -                         -

Cus5              10             -                         20

We should exclude nul values records where we have 2 nulls or 3 nulls. We can keep record with only one null value

Expected

D1               D2                D3

Cus5              10             -                         20

• ###### Re: Remove null values at measures

Hi,

you can try this expression:

SUM({<CustID={"=RANGENULLCOUNT(D1,D2,D3)<2"}>} D1) in D1 expression column and so on until D3/Dn

• ###### Re: Remove null values at measures

Hi! Try this:

```Data:
CROSSTABLE (Dimension, Value)
Customer,
D1,
D2,
D3
INLINE [
Customer,D1,D2,D3
Cus1,100,,
Cus2,,20,
Cus3,10,,
Cus4,,,
Cus5,10,,20
];

Temp_A:
Customer AS Temp_Customer,
Dimension AS Temp_Dimension,
IF(LEN(TRIM(Value))=0, 0, 1) AS Value_Count
RESIDENT Data;

Temp_B:
LEFT JOIN (Data) LOAD
Temp_Customer AS Customer,
Sum(Value_Count) AS Value_Count
RESIDENT Temp_A
GROUP BY Temp_Customer;
DROP TABLE Temp_A;

Final:
Customer,
Dimension,
Value
RESIDENT Data
WHERE Value_Count >= 2;
DROP TABLE Data;

```
• ###### Re: Remove null values at measures

Can you as how to do with expression at report

• ###### Re: Remove null values at measures

Try this:

1) Go to Add-ons -> Data handling and uncheck "Include zero values"

2) Use this expression in your pivot table:

```If(Aggr(Count(Value),Customer)<2, Null(), Value)

```
• ###### Re: Remove null values at measures

i tried thsi laready, but no luck

1) Go to Add-ons -> Data handling and uncheck "Include zero values"

2) Use this expression in your pivot table:

• ###### Re: Remove null values at measures

Hi Nagamani,

Sample Script:

CrossTable(Group,Value)

load * Inline [

Cust,D1,D2,D3

Cus1,100,,

Cus2,,20,

Cus3,10,,

Cus4,,,

Cus5,10,,20

];

Try this expression:

=IF(AGGR(count({<Value={"=len(Value)>0"}>}Group),Cust)>1,Cust)

• ###### Re: Remove null values at measures

Hi

I have created pivot table with one dimension iin column and one dimension in row and another measure.

I have null values for few records as below. we should exclude reocrds with null values at measure

report is like below

D1,D2,D3 are values for  column in columns section

Cus1,Cus2 are values for column in row section

100,20,- are values in measures section

D1               D2                D3

Cus1            100               -                     -

Cus2              -                 20                   -

Cus3              10             -                         -

Cus4              -             -                         -

Cus5              10             -                         20

We should exclude nul values records where we have 2 nulls or 3 nulls. We can keep record with only one null value

Expected

D1               D2                D3

Cus5              10             -                         20

• ###### Re: Remove null values at measures

i have large data, i cannot use data in script