How to create ‘Errata’ report in Qlik Sense?

    Introduction:

    The famous phrase in normal business reporting world is “garbage in, garbage out”.  It refers that whatever data available or feed in the source system will be reflected in the reporting or dashboard front end. Many people may think that the Qlik Sense is a visualization tool.  But it can add feather on their ‘business discovery’ capability to pinpointing the erroneous data to be cleaned in the source system and take necessary action to handle such type of data.

     

    In this document, we are going to discuss about the step-by-step method to get the erroneous data and show it on the front end. This is sample to get few erroneous data and may expand to any level of details.

    Final Output:

    errata1.png

    The above page showing how many distinct product numbers has erroneous data on the following:

    • 0 discount products
    • Colour is blank
    • Size is not indicated
    • Weight is not indicated

    Methodology:

    For this example, I used the sample database of Microsoft SQL Server – Adventure Works 2012 schema. I created a separate view to extract the erroneous data from the database.  My view output as follows:

    errata2.png

    Let us create a Qlik Sense application on the top of the above data for this exercise.

    Step 1: Create an app

    Create a new Qlik Sense application and name it as ‘Errata’ (optional naming)

    Step 2: Create a data connection

    Open the app and create an OLE DB connection to connect the SQL Server Adventure Works schema.

    Step 3: Extract data

    Choose the view and insert the script to extract the data.

    Step 4: Create code for Errata

    Create a new tab and write the following code to extract the erroneous data as follows:

    Errata:

    LOAD

                     ProductNumber                           as            Link_Exception,

                     count(ProductNumber)                 as            Product_Exception,

                     'Weight is not indicated'               as            Link_Inline

    Resident MasterData

    Where Weight<=0 or isnull(Weight) Group by ProductNumber;

     

    Concatenate(Errata)

    LOAD

                     ProductNumber                            as            Link_Exception,

                     count(ProductNumber)                  as            Product_Exception,

                     'Size is not indicated'                    as            Link_Inline

    Resident MasterData

    Where Size<=0 or isnull(Size) Group by ProductNumber;

     

    Concatenate(Errata)

    LOAD

                     ProductNumber                             as            Link_Exception,

                     count(ProductNumber)                   as            Product_Exception,

                     '0 Discount Products'                     as            Link_Inline

    Resident MasterData

    Where Discount<=0 or isnull(Discount) Group by ProductNumber;

     

    Concatenate(Errata)

    LOAD

                     ProductNumber                             as            Link_Exception,

                     count(ProductNumber)                   as            Product_Exception,

                     'Color is not indicated'                    as            Link_Inline

    Resident MasterData

    Where isnull(Color) or Trim(Color)='' Group by ProductNumber;

     

    Some explanations on the above code:

    We are getting the unique value of the product number which has no weight, no colour, no size and 0 discount indicated.  So, we need to separately create a table to get all information and link with production number as a key.  The filters conditions are the data which has null values and blank and got 0 values.

     

    Step 5: Create a inline table

    The following code has to create a inline table for which the values to be used in charts.

     

    Exceptions_Inline:

    LOAD * Inline [

    Link_Inline, Exceptions

    Weight is not indicated                  , Weight is not indicated

    Size is not indicated                        , Size is not indicated

    Color is not indicated                      , Color is not indicated

    0 Discount Products                        , 0 Discount Products

    ];

     

    Once done reload the application.

    Step 6: View the data model

    Check the data model viewers to verify all the tables are linked properly as follows:

    errata3.png

    Step 7: Create a new sheet

    Now it’s time to create errata reports.  So create a new sheet called ‘Errata’.

    Step 8: Add bar chart

    Add a bar chart with the following dimensions and measures.  The bar chart may be horizontally oriented in order to view the dimensions to be readable fully.

    1. Dimensions = Exceptions
    2. Measure = Count(Product_Exception)
    3. Number formatting = Auto
    4. Show values on data points and colours are multicolour (persistent)
    5. Provide relevant name for x, y and chart titles.

    Once done the above the chart may look like as follows:

    errata4.png

    Step 9: Add PIE chart

    To show the % of erroneous data, a pie chart can be added to show the volume of errata in percentage as follows:

    errata5.png

    Step 10: Add a detailed report

    In order to view and export data into excel, a table can be created as follows which will be more helpful for users to send the report to rectify the errors among their colleagues.

    errata6.png

    Step 11: Add a list box

    A list box to show the product number will be further helpful for users.

    Once all the above steps done, the output will be as expected as shown in the beginning of this document.

    Conclusion:

    Qlik Sense is not just a visualization tool.  We can use it as much as its capability in various aspects.  By having this errata reports, users will tend to fix and correct their data moving forward.  So, the amount of erroneous data will be reduced in future.  This type of solution will attract more customers and users to Qlik, I hope.

     

    Enjoy ‘Qlik’ing! Thank you so much for your time to read my document.