Tuesday 15 April 2014

Scenario 17: How to assign same id for specific group of records

Scenario 17: How to assign an id for specified group of records and achieve target below.

Source
Column1
A
B
C
*
D
E
F
G
*
H
I
J

Target
Column1
D
E
F
G

Analysis: As we have to load only those data which are in between two stars (*). If we can assign a same id for all records which are appearing after occurrence of first star till next star then filter it based upon id then we are done.

Practically:
Step 1: 
Drag and drop source and target metadata to mapping designer.

Step 2:
Expression
Create Four ports
START (Variable Port) = IIF(COLUMN1='*',1)

PASS (Variable Port)= IIF(START=1, OLD_START+1, IIF(START=0 AND OLD_START=0, 0, IIF(START=0 AND OLD_START=1, OLD_START)))

OLD_START (Variable Port)= PASS
PASS_OUT (Output Port)= PASS

Step 3:
Filter
Filter Condition =  COLUMN1!='*' AND  PASS_OUT = 1

Step 4:
Connect to Target from Filter Transformation.

BINGO!!!


Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com

2 comments:

  1. Hi Harsh
    I have a doubt when the start value will be 0 .

    Thanks in Advance

    ReplyDelete
    Replies
    1. Hi, Subrat
      When Column1 Value is not equal to star (*) then you will get START value as 0 as data type of START port is Integer so, when condition is not TRUE it will automatically take 0. Implement it practically for better understanding.
      Thanks

      Delete