In this tutorial, we will learn how to use SAP data services Case Transform. Case transform provide us a means to implement "If... Then... Else" logic in data services. In the end a short video is provided with this tutorial to give you a hands-on feel in data services.

This article is part of our comprehensive data services tutorial "Learning SAP Data Services - Online Tutorial", you may want to check that tutorial first if you have not already done so.

What is 'Case Transform'?

Case transform is basically like 'select... case' in VB or 'switch... case' in C programming language. This is also similar to the "Router Transform" available in Informatica ETL tool. Whenever there are multiple filter conditions or we want to route data to multiple pipe lines based on some conditions, we use case transform.

Video Illustration on how to use Case Transform

We will use the EMP table (having employee names etc. along with their corresponding departments) to demonstrate the use of case transform. If you are not following this tutorial series from the beginning and do not know how the EMP table look like, just remember that, among other things, EMP table has employee name (column name ENAME), employee number (column name EMPNO) and department no (DEPTNO).

Now suppose, we want to generate 3 different flatfiles based on whether an employee belongs to department 10, 20 or 30. To do this, we will use a 'Case Transform' in the data flow and reroute the data based on the department numbers.

Options available under case transform

As you have seen in the video above, there are 3 specific options that are available inside case transform. Let's understand what are those

Produce Default Output

Case transform allows you to create your own labels (or groups) and define the condition or criteria, passing which data will flow into that group. However, it may also happen that incoming data does not match with any of the conditions in the label. Let's say, we created labels for department 10, 20 and 30. But the input record is associated with department 40. What will then happen to this record? By default, this record will flow into a default group. The label of this group is also 'default'.

However, if you are sure that the incoming data will not flow into any other groups apart from what you have designed or even if it flows, you do not need the data for the default group, you can turn-off the check box that says "Produce default output with label".

Row can be TRUE for one case only

Uncheck this option, if there is a possibility that one incoming row will satisfy multiple label conditions. For example, let's say the condition for the first label is deptno > 10 and second label is deptno > 20, then one incoming record with department number 30 will match both the conditions. In such cases, if you have 'Row can be TRUE for one case only' checked, the data will only go to any one of the labels.

Preserve Expression Order

As explained above, where we have one row matching more than one labels and we have ticked 'Row can be TRUE for one case only', the row will flow into any one of the satisfying labels. But to which label, the row flows depends on this condition. If we have this check box checked, the row will follow the order of the labels and accordingly flow into the first label.

Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • Fools Guide to BODS - Introductory Tutorial

    This article is a guide to the readers who are totally ignorant about the tool SAP Business Objects Data Services. It is presented in a very simple way so that readers would be familiar with common terms, terminologies and would be able to run...

  • How to install SAP BODS - Standalone

    This article is a step by step guide on how to install standalone SAP BODS Batch Job Server, Client Components, Web based Administrative Console and how to configure the same.

  • Working with Data Services Flatfiles

    In this article we will try to cover all the possible scenarios related to flatfiles in SAP Data Services.

  • One Stop to SAP BODI/BODS

    BODI Business Objects Data Integrator or BODS Business Objects Data Services is a GUI workspace that allows to create jobs that extracts data from heterogeneous sources, transforms that data using built-in transforms and functions to meet business...

  • SAP BODS Cluster Installation

    This article is a step by step guide on how to configure SAP BODS for High Availability using Windows Cluster services. To take advantage of fail-over support for SAP BusinessObjects Data Services services in a Windows Clustering Environment,...

  • Learning SAP Data Services - Online Tutorial

    We are pleased to start this new tutorial page for SAP BusinessObjects Data Services (BODS). If you do not know SAP Data Services (BODS) yet but wish to master this ETL tool, you have come to the right place. Even if you are already familiar with...

  • How to use SAP Data Services Case Transform

    In this tutorial, we will learn how to use SAP data services Case Transform. Case transform provide us a means to implement "If... Then... Else" logic in data services. In the end a short video is provided with this tutorial to give you a hands-on...

  • Data Services Metadata Query Part 2

    This article is a continuation of the previous topic related to Data Services Metadata Query. Let us explore more into the Data Services Repository Metadata.

  • Data Services Scenario Questions Part 1

    In this tutorial we will discuss some scenario based questions and their solutions using SAP Data Services. This article is meant mainly for Data Services beginners.

  • Table Comparison Transform to Implement Slowly Change Dimension (SCD) in Data Service

    In this tutorial we will learn a new SAP Data services transform, known as Table Comparison Transform and we will see how we may use this transform to implement "slowly changing dimension" (SCD) Type - I. Like before, we have added a video tutorial at the...