Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

8 Replies
mindaugas2048
Partner - Contributor III
Partner - Contributor III

Hi,

you can try this expression:

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

Ivan_Bozov
Luminary
Luminary

Hi! Try this:

Data:

CROSSTABLE (Dimension, Value)

LOAD

     Customer,

     D1,

     D2,

     D3

INLINE [

Customer,D1,D2,D3

Cus1,100,,

Cus2,,20,

Cus3,10,,

Cus4,,,

Cus5,10,,20

];

Temp_A:

LOAD

     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:

LOAD

     Customer,

     Dimension,

     Value

RESIDENT Data

WHERE Value_Count >= 2;

DROP TABLE Data;

vizmind.eu
Anonymous
Not applicable
Author

i have large data, i cannot use data in script

Anonymous
Not applicable
Author

Can you as how to do with expression at report

Anonymous
Not applicable
Author

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

Ivan_Bozov
Luminary
Luminary

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)

vizmind.eu
Anonymous
Not applicable
Author

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:

kaanerisen
Creator III
Creator III

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)

Untitled.png