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
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
Hi Harsh
ReplyDeleteI have a doubt when the start value will be 0 .
Thanks in Advance
Hi, Subrat
DeleteWhen 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