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

    Remove null values at measures

    nagamani vadisala

      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
          Mindaugas Lekerauskas

          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
            Ivan Bozov

            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;
            
            
            
            
            
            
            
              • Re: Remove null values at measures
                nagamani vadisala

                Can you as how to do with expression at report

                • Re: Remove null values at measures
                  nagamani vadisala

                  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
                  nagamani vadisala

                  i have large data, i cannot use data in script