Use Conditional Logic in Sequel Data Access to Convert Data into Meaningful Information

Let’s face it, the amount of data that we have is only continuing to grow, which will become harder to manage and understand. Per IDC Research, 90 percent of data in the world today was created in the last two years, and in 2016, we entered the zettabyte era.

A huge challenge for everyone is finding the best way to convert all their data into meaningful information. But that can be tricky. 

If you're just getting started with business intelligence, begin by learning why data needs to be turned into information. Read the article >

But If You're Already Using Sequel...

Transforming data into information is easy! With Sequel Data Access, data can be converted, bucketed, and enhanced to take results to the next level.

By enhancing views with Conditional Logic, you can transform data into information exactly how your users need to see it!

Using Sequel’s CASE function, you can gather unformatted and/or meaningless data and transform it into information that can be used by everyone from upper management to sales and accounting. Using Conditional Logic allows numbers for departments or region values to be re-stated into department and region names. Values based on a department, year, quarter, and so forth, can be ‘bucketed’ or grouped accordingly.

With CASE functions, users are able to conditionally:

• Bucket or group data by department, years, etc.
• Describe data
• Run calculations based on a value
• Calculate a value (build logic into a field)
• Validate data (e.g. date value) before converting it

Case statements are a Conditional Expression that can be used in the SELECT, WHERE, or HAVING clause. Think of it as an IF – THEN – ELSE type of logic; IF this, THEN do that, or ELSE do something different. A CASE expression can be used to define new columns, perform calculated tests against another expression result, or be used as a further component of another expression.

Users who are new to your data structure, or users that have been using it for some time, can still struggle to interpret the data that is being collected and distributed. With Sequel’s ability to re-state data by using the CASE function, you are able to give your users information in a clean, easy to read format, allowing them to better analyze data and make important business decisions.

The CASE expression has two forms. The first form is considered the simple form because it only allows an equal comparison on the WHEN clause and an optional ELSE statement. The second form allows you to make more complex comparisons well beyond the equal sign (e.g. <, >, CONTAINS, BETWEEN, etc.).

The CASE Expression – Simple Form

To demonstrate the simple form, we will create an equal comparison to conditionally describe a two-digit customer type field with a better description. Using the CASE function with the CTYPE field form the SEQUELEX/CUSTMAST, the data becomes more readable:

…CASE ctype
WHEN "CT" THEN "Contract"
WHEN "RG" THEN "Regular"
ELSE "Special"
END

Image

To demonstrate the capability of the more complex form, we will compare the sales between the current year and previous year. The first statement is calculating the current sales year and month-to-date.

... case when sales_yr=year(current date) and mnth=month(current date) then  qty*actsp else 0 end

Image

Common Use of Case Statements - Validating and Converting Dates

With Sequel, you can convert a numeric or character date field into a true date data type field quickly and easily. You should be able to verify whether the data can be converted to a valid date. If not, replace it with a NULL value (shown as ‘n/a’) to avoid data mapping errors. Once converted, you can sort on that date field and select records by date range. 

The following Sequel example is converting a numeric date field into a true date data type field:

… CASE when VALID_DATE(mydatefld, "*MDY")=1
Then CVTDATE(mydatefld,mdy)
Else NULL
End

Image

Step by Step – Building Conditional Logic Into Your View

The following example will walk you through building a view and using the more advanced form of conditional logic. The CASE function can be combined with the SUM function to conditionally summarize data. This can be done easily to create multiple columns, especially when comparing values side by side. In this example we will analyze sales information between the current year and the previous year.

Step 1 - Build the view (raw data) selecting the sales information for all regions and years. 

Step 2 - Enhance the view by adding in Conditional Logic and run-time variables.

At run time, the user will be prompted for the Region value, while the Current and Last Year values are automatically calculated and passed on by Sequel—without prompting the user. The year value parameters will be used in the calculated derived fields and within the header of those columns as shown below.

Image

The derived fields are summing revenue and cost of goods sold IF the sales year is equal to last year (using a variable) or the current year respectively. The variables &lyear and &cyear are decimals and the values are dynamically calculated at run time (e.g. SQL(YEAR(CURRENT DATE)– 1)and SQL(YEAR(CURRENT DATE). 

Image

With the final results, the variable values for the current year and last year are calculated and used within the header of the columns. The condional logic that we added by using the CASE function, returned information in multiple columns. This allows the user to easily analyze the current year Cost of Goods sold and Revenue relative to the previous year.

Image

Do you have data that doesn’t mean much to you? Do you need a solution that can convert that data into meaningful information?

With Sequel’s CASE function, you can convert meaningless data into meaningful information. Users can now get the information they need and easily analyze it. From converting numeric date fields into a true date data type to performing conditional calculations and building buckets of information, Sequel makes it possible for users to gain insight and make informed business decisions.