1) How to convert single row from source to three rows in target
- In normalizer create column1 and increase occurs to 3 so that it create 3 inputs
2) How to Split the non-key columns to separate tables with key column in both
3) How to separate duplicate and non duplicate records to a separate table.
- In sorter transformation sort on first port in ascending order
- In aggregator transformation create a new port and check group by port on column1, then take count on same column as count(address1).
- In router create two groups and give conditions as count = 1 and count >1.
4) How to Retrieving first and last record from a table/file.
- Give rank as top and bottom for both the rank transfomations and set number of ranks as 1.
- Create two instances of target.
5) How to remove footer from your file ???????????
- In filter give the condition as instr(col1,'footer')=0, which removes the word footer from the footer file.
6) How to send 1st half rows to target1 and second half rows to target 2 ?
- Generate count in aggregator and group by on empno
- Generate seqno in expression
- Use joiner to join both aggregator and expression
- In router give two conditions as ' o_seqno <= count_rec/2' and 'o_seqno >= count_rec/2
7) How to send alternate records to 2 different targets ?
- Generate sequence and connect to expression
- In expression transformation, add two ports as Even and Odd.
Give the syntax for Odd port as ' NEXTVAL%2 = 1' & Even port as 'NEXTVAL % 2 = 0'.
- In router transformation, create two groups Even and Odd. Give the filter condition for Even as ' Even = 1' and for Odd as ' Odd = 1'.
8) How to send record into target in cyclic order ?
- In sequence generator set start value as 1, end value as 3 and check cycle option to repeat 1 to 3 cycle.
- In router create 3 groups and give condition as Nextval = 1, Nextval = 2, Nextval = 3 and connect outputs to target.
9) How to generate target file names dynamically for flat files?
- Go to the target and select top right 'F' option, a new column will be generated as filename.
- In expression create a 'Dynamic file' name port and give its any name like 'Test_file' or ' To_char(sessstarttime,'yyyymmdd24').
- Connect Dynamic file to filename port in the target.
10) How to process 'CurrentlyProcessedFileName' into the target?
After importing source definition, go to properties tab and select the option 'add Currently Processed Flat File Name'. As a result the filename will be processed into the target.
11) How to remove special characters from a string ?
- In expression transformation create a o_name port and give a synatax as 'REG_REPLACE (NAME,'[^A-Z0-9a-z ]','').
- This expression removes all the special characters except Captial letters, Small letters and numbers.
12) How to load the last 3 rows from a flat file into a target?
- Sort on Empno port and connect to Expression transformation as well as Aggregator transformation
- Create dummy ports in both the expression and Aggregator, inorder to join both the transformation with joiner
- Create count port in aggregator transformation
- Check the sorted input option in both the aggregator and joiner transformations
- In joiner give condition as dummy= dummy1
- In router or filter give the condition as Count - o_seqno <=2, as a result last 3 rows will be inserted into the target.
- In normalizer create column1 and increase occurs to 3 so that it create 3 inputs
2) How to Split the non-key columns to separate tables with key column in both
3) How to separate duplicate and non duplicate records to a separate table.
- In sorter transformation sort on first port in ascending order
- In aggregator transformation create a new port and check group by port on column1, then take count on same column as count(address1).
- In router create two groups and give conditions as count = 1 and count >1.
4) How to Retrieving first and last record from a table/file.
- Give rank as top and bottom for both the rank transfomations and set number of ranks as 1.
- Create two instances of target.
5) How to remove footer from your file ???????????
- In filter give the condition as instr(col1,'footer')=0, which removes the word footer from the footer file.
6) How to send 1st half rows to target1 and second half rows to target 2 ?
- Generate count in aggregator and group by on empno
- Generate seqno in expression
- Use joiner to join both aggregator and expression
- In router give two conditions as ' o_seqno <= count_rec/2' and 'o_seqno >= count_rec/2
7) How to send alternate records to 2 different targets ?
- Generate sequence and connect to expression
- In expression transformation, add two ports as Even and Odd.
Give the syntax for Odd port as ' NEXTVAL%2 = 1' & Even port as 'NEXTVAL % 2 = 0'.
- In router transformation, create two groups Even and Odd. Give the filter condition for Even as ' Even = 1' and for Odd as ' Odd = 1'.
8) How to send record into target in cyclic order ?
- In sequence generator set start value as 1, end value as 3 and check cycle option to repeat 1 to 3 cycle.
- In router create 3 groups and give condition as Nextval = 1, Nextval = 2, Nextval = 3 and connect outputs to target.
9) How to generate target file names dynamically for flat files?
- Go to the target and select top right 'F' option, a new column will be generated as filename.
- In expression create a 'Dynamic file' name port and give its any name like 'Test_file' or ' To_char(sessstarttime,'yyyymmdd24').
- Connect Dynamic file to filename port in the target.
10) How to process 'CurrentlyProcessedFileName' into the target?
After importing source definition, go to properties tab and select the option 'add Currently Processed Flat File Name'. As a result the filename will be processed into the target.
11) How to remove special characters from a string ?
- In expression transformation create a o_name port and give a synatax as 'REG_REPLACE (NAME,'[^A-Z0-9a-z ]','').
- This expression removes all the special characters except Captial letters, Small letters and numbers.
12) How to load the last 3 rows from a flat file into a target?
- Sort on Empno port and connect to Expression transformation as well as Aggregator transformation
- Create dummy ports in both the expression and Aggregator, inorder to join both the transformation with joiner
- Create count port in aggregator transformation
- Check the sorted input option in both the aggregator and joiner transformations
- In joiner give condition as dummy= dummy1
- In router or filter give the condition as Count - o_seqno <=2, as a result last 3 rows will be inserted into the target.
OR
Rank transformation can also be used here to make it simple.
- Select bottom 3 option in rank transformation & connect it to target.
13) How to load the first 3 rows into target1, last 3 rows into a target2 and remaining rows into target3 ?
- This logic is almost same as previous one, but additional conditions and targets are added as per the requirement
- In router first condition should be O_SEQ_NO <= 3, second condition should be COUNT - O_SEQ_NO <= 2
- Now connect all to the targets 1,2 and 3
- Default group will return remaining rows, which are not mentioned in condition
14) Scenario, if you have source with 10 records and 3 target table, your target1 output should be 1-2 records, target2 output should be 2-7 records, target3 output should be 8-10 records.
- Sort on Empno port and connect to Expression transformation as well as Aggregator transformation
- Create dummy ports in both the expression and Aggregator, in order to join both the transformation with joiner
- Create count port in aggregator transformation
- Check the sorted input option in both the aggregator and joiner transformations
- In joiner give condition as dummy= dummy1
- In router, create two different groups and name it as First and Last
- Give the First group condition as Seqno <=2, Last group condition as Count - o_seqno <=2 as a result last 3 rows will be inserted into the target
- Finally connect default port to target 3, remaining records will be loaded into the target
No comments:
Post a Comment