+++&gt; 2.How to Case n Merge

Case & Merge Transformations

Case Transformation is used to break a single set of data in to multiple sets using a condition.

For example we will take a spread sheet having the data Employee Name & Region ID and we need to break
the Data (Single set of Data) in to multiple sets based on the region ID.

The Input Data is as follows

Name	RegionID
sam	1
ash	2
egg	3
pre	2
prom	4
kish	4
thiru	3
kiran	2
trup	1
aaa	0
bbb	0
cccc	7
dddd	8
eeee	9

Now we need to break that data based on the region ID .

Demo

Create a spread sheet as above.

Call this spread sheet to Data Services Using file format from local Object library.

Click on New and Give the below Credentials

Directory : Path to desktop where the file is saved

File Name :- Case.Xls

Work Sheet :- Sheet1

Check Use First Row values as Col Names and click on “Import Schema “ button.

And click on Ok.

Now you added Imported the spread sheet data in to the Data Services.

Now Create a Batch Job “Case_N_Merge” and under it,

Create a Dataflow “DF_Case_Merge” and in the data Flow Drag the Flat File “Case” in to the designer window.

Now Drag a Query from the Right side pane/ Query under the Query transform under local object library.
Connect the Spread sheet to the Query, Open the Query and drag the fields from schema in to schema out.

Now Drag “ Case” transformation to the designer window,which is present under the Platform under the Query
Transforms under the local object library, Rename it as Case_Region and connect the Query to Case as below

Now click on Case_Region to open the case transformation

Now we need to Give the cases which will break the single set of data in to multiple sets in our case region id.
So click on ADD button Give the Label “ west” and drag and drop Region ID from schema to the below case
window and assign values = 1 as shown below

Similarly do for the remaining regions Nrth, Sth, Est. and change the Produce Default output value with label
as UNK (Unknown)

Now click on back

and create 5 Template tables to hold the data ( Multiple sets) and connect the case to
the tables. While connecting just make sure u connect the respective case to the respective table.

Similarly connect case to the remaining tables and UNK to unknown table. Now save the job and Run the job.
So now the output tables should the data as below.

West = RegionID 1

North = RegionID 2

South = RegionID 3

East = RegionID 4

Now after running the job click on the magnifying glass to check the data.

Now to do the Merge Transformation we will make all the region tables (West, East, North, South, Unknown)
in to permanent tables. So that we will use them as multiple sources and use the merge transformation to
combine multiple sets in to single set. To make the template table as a permanent table,
Right click on the table and click on import.

Merge Transformation Combines Multiple sets of information in to single set, Provided all the multiple sets
should have the same table Structure (Field name, data type and Length).Create another dataflow DF_Merge1
and delete the DataFlow “ DF_Case_Merge”.Now Drag all the above created permanent tables to the designer
under the Data Flow“ DF_Merge1”as shown below.These tables will be available under your DataStore Training.

Now Drag the Merge Transformation which is present under the Platform in Query transformations in
local object library and connect all the 5 tables to the Merge.