3 Replies Latest reply: Sep 11, 2018 4:38 PM by Nagesh Setty RSS

    NULL Counts in Qliksense

    Sravan Paritala

      Hello

       

      I need a small help in transposing the output that i currently have on my Qliksense dashboard.

       

      Actual Requirement:

      Would like to build a table that shows the list of columns in a table and corresponding count of records where a particualr column is NULL. For instance in the attached spreadsheet, I have column #1 has 20. So column #1 has 20 records that are NULL in my database.

       

      After going through various posts, i managed to get the NULL counts using the below formula.

      Sum(if(isnull(column1) OR Len(Trim(column1)) = 0, 1, 0))

       

      The problem is that current table chart is spread horizontally but i want it vertically. Please refer to the attached spreadsheet for my expected output format.


      Thank you and appreciate your help


      Note: If there is a better way to display the NULL counts of all columns in a particular table using qliksense, please share.

        • Re: NULL Counts in Qliksense
          Nagesh Setty

          Use CrossTable approach.

          FinalData:
          CrossTable('Attribute Name', '# Recordcount with NULL')
          LOAD
              Data,
              "Column 1",
              "Column 2",
              "Column 3",
              "Column 4",
              "Column 5",
              "Column 6",
              "Column 7",
              "Column 8",
              "Column 9",
              "Column 10",
              "Column 11",
              "Column 12",
              "Column 13",
              "Column 14",
              "Column 15",
              "Column 16"
          FROM [lib://mydata/crosstable.xlsx]
          (ooxml, embedded labels, table is Sheet1);
          drop Field [Data];
          
          
          
            • Re: NULL Counts in Qliksense
              Sravan Paritala

              Thank you Naresh! I could transpose it now. But however, i could not the required output i.e. the counts

               

              In the sample data that i provided i had count of rows. But when i connect to database and extract the data into Qliksense and used it on the chart I am getting everything as 0.

               

              This is what I hv followed

              //Connect to database using the connection string

              //Output table name:

              //Cross table logic as described in your post

              // Load and listed all the columns from my database for which I wanted to know NULL counts

              // SQL Script to populate the data from database.

               

              Once the data load is complete. I tried creating a Table chart with Attribute Name and # Record count with NULL as my column names. When i did I got 0. But I am expecting to see the count of records that has NULL for column 1 , column 2 etc.

                • Re: NULL Counts in Qliksense
                  Nagesh Setty

                  Test your output using your usual load statement.

                   

                  Once you confirmed the NULL counts, and column names are appearing as in your excel sheet.

                   

                  Use

                   

                  NewData:

                  CrossTable ([Attribute Name], [# Recordcount with NULL]) Load *

                  Resident <your Table>

                   

                   

                  Compare both <your Table> and <NewData> in table viewer or in actual visualization (straight table)