NULL Counts in Qliksense

[sravan2013]

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.

[nsetty]

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];

[sravan2013]

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.

[nsetty]

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)