Business today heavily relies on data analysis to make crucial business decisions. When it comes to data analysis, it is important to understand the quality of the data. Various aspects contribute to data quality with common checks involving the identification of duplicates, overlapping values, null values, empty strings, invalid ranges, and more.
Let us explore how the conditional formatting feature helps you to perform some data quality checks for effectively assessing and analyzing data.
Using conditional formatting to improve data quality
Consider a customer complaints data of a bank. Let us determine whether the bank responded on time and if the consumer disputed the bank's response.
To get the required data, run the following query:
SELECT “Complaint ID”, ”Date_received”, “Product”, “Company”, “Timely response?”, “Consumer disputed?” FROM Q.BANK_CUSTOMER_COMPLAINT_DATA
The results of the query show us that the Consumer disputed column shows the <NULL> value in some entries. The ideal response for this column should either be either “Yes” or “No”.
You can easily identify the <NULL> values if they are formatted differently than the other data. The Conditional Formatting feature improves the quality of the data and makes it more useful.
To apply conditional formatting for the <NULL> values, complete the following steps:
1. Open the main panel of the Form by pressing F9.
2. Set a condition by clicking View > Conditions, and then adding the following condition:
ID=1, Condition Name=isnullcheckon6, Conditional Expression=isNull(&6), Pass Nulls=NO.
3. Add a rule for the condition by clicking View > CNDLFormat and adding the following rule:
ID=1, Condition ID-Name=1-isnullcheckon6, Column Num=6, Align=CENTER, Edit=C, Foreground Color=YELLOW.
Note: This rule will change the color of the text foreground to Yellow for all the cells of the Consumer Disputed column whose value is NULL and will also change the NULL text to ‘-‘.
4. To generate the report, press F12.
Using conditional formatting for data analysis
Let us analyze the data by highlighting the banks that did not respond on time.
Apply conditional formatting by completing the following steps:
1. Open the main panel of the Form by pressing F9.
2. Set a condition by clicking View > Conditions, and then adding the following condition.
ID=2, Condition Name=nocheckon5, Conditional Expression=(&5=’No’), Pass Nulls=NO
ID=3, Condition Name=yescheckon5, Conditional Expression=(&5=’Yes’), Pass Nulls=NO.
3. Add a rule for the condition by clicking View > CNDLFormat and adding the following rule:
ID=2, Condition ID-Name=2-nocheckon5, Column Num=4, Align=RIGHT, Edit=C, Foreground Color=RED.
ID=3, Condition ID-Name=2-yescheckon5, Column Num=4, Align=LEFT, Edit=C, Foreground Color=GREEN.
Note: With these new rules, banks who did not respond on time will get highlighted with red color and it will get right aligned. Similarly, banks who did respond on time will get highlighted with green color and it will get left aligned.
4. To generate the report, press F12.
The report highlights the banks that did not respond on time. These entries often coincide with the NULL values in the Consumer Disputed column.
The Conditional Formatting feature helped to establish the following pattern in the data: NULL values in the Consumer Disputed column correspond to the instances where the banks did not respond on time.
This video demonstration shows you how to use the Conditional Formatting feature to uncover the patterns in the data and provide meaningful insights, thus facilitating checks for assessing the overall data quality.
https://video.ibm.com/recorded/133308452
#Db2QMF #Db2Tools #QMF #zClient#conditionalformatting #form#report