Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

manigunta
Contributor

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
New Contributor III

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

Ivan_Bozov
Valued Contributor

Re: Remove null values at measures

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;

manigunta
Contributor

Re: Remove null values at measures

i have large data, i cannot use data in script

manigunta
Contributor

Re: Remove null values at measures

Can you as how to do with expression at report

manigunta
Contributor

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

Ivan_Bozov
Valued Contributor

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)

manigunta
Contributor

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:

kaanerisen
Contributor III

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)

Untitled.png