cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Identifying when a Control Break occurs in a pipeline

hkaplan
Former Employee

Have you ever needed to identify when a control break occurs in your incoming data?
Having this ability can save you considerable processing resources in cases where your incoming data is sorted on last names and you only need to perform calculations or lookups when a change occurs on last name.

Here is the pipeline i put together to demonstrate how to identify control breaks in your incoming data.
8a2cff2d7962a19882f5f831bd579b1a133cdf9c.jpg

the first snap generates the following input data:
5696372b5de141d070ce835ace51209448ee9d92.png

Using a sort snap i then sort the incoming records by lastname
066bfd47eba1c73d3a1f37c6d53f9a4fcfe7dc73.png

In order to identify when a control break occurs, we need to compare one record to another and to do this we need to join one record to itโ€™s previous record. To accomplish this we will use one of the snap functions accessible in as an expression in a mapper snap.
Here you can see where i am moving snap.in.totalCount out as $SnapInTotalCount.
ea8f27684dc0b9828a70bfdee229bbe4694e1d51.png

Next i use a copy snap to make two copies of the incoming data which i will then join back together using a join snap.

I now need to join the current record from one input stream to the previous record from the other input stream and the following trick below is how i accomplish this. I subtract one from the left pathโ€™s $SnapInTotalCount and join it to right pathโ€™s $SnapInTotalCount.
*note i subtracted 0 to keep the data formats aligned.

1c17020546a1a7c1e4821ed6bd16f179de6c6644.png

i now have record 1 on the same line as record 0, record 2 on the same line as record 1 and so on.
Now all i have to do to set my flag is compare LastName to input1_LastName.

1747bfa13e37fc28b48b2f2215c84c3d64e6a3e9.png

Using a condition snap and the following conditional expressions, i am able to set my control break flag ($CD_FLAG) to either T or N.

ce8a1f52ebddc498c5e0cf51ed85df72951fa690.png

Below are the final results and as you can see CB_FLAG is set to โ€˜Yโ€™ anytime there is a change in LastName.
I can now use CB_FLAG downstream in my pipeline in a router or filter snap to avoid unneeded processing.
d7be5b4c02829e284ec30f781a1c2d4a76fe31af.png

Control Break Pipeline Example_2017_02_28.slp (12.4 KB)

0 REPLIES 0