If the source has duplicate records as id and name columns, values: 1 a, 1 b

If the source has duplicate records as id and name columns,
values:
1 a, 1 b, 1 c,
2 a, 2 b,
the target should be loaded as 1 a+b+c or 1 a||b||c, what
transformations should be used for this?

Question Posted / jey.dgl

4 Answers
10321 Views
ABC, Cap Gemini, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

If the source has duplicate records as id and name columns, values: 1 a, 1 b, 1 c, 2 a, 2 b, t..

Answer / shankar

SQ--->SORTER---->EXP---->AGR---->TGT

1.First pass all records into sorter
seect sort key on id.
2. An pass records into EXP
In exp creat 4 pors like
v_id=id
v_name=nme
v_name1=iif(id=v_id,name||'+'||v_name,name)
o_name=v_name1
3.Next pass all to agr
ini agr creat one port like
o_name_t=last(o_nme).
4.Pass to Target

Is This Answer Correct ?

8 Yes

0 No

If the source has duplicate records as id and name columns, values: 1 a, 1 b, 1 c, 2 a, 2 b, t..

Answer / babu

Sorry Shankar,

small mistake in your logic,

Exp Trans:
ID (I/O)
NAME (I)
V_NAME=IIF(ID=V_ID,V_NAME||'+'||NAME,NAME) (V)
V_ID=ID (V)
O_NAME=V_NAME (O)

=============
remaining is same.

Is This Answer Correct ?

2 Yes

0 No

If the source has duplicate records as id and name columns, values: 1 a, 1 b, 1 c, 2 a, 2 b, t..

Answer / mithilesh kumar rai

First Use Expression and then Use Aggregater
After Expression U will receive
1 a
1 a b
1 a b c
2 a
2 a b
2 a b c

then use aggregater group by key 'ID'
After Aggregater u will receive
1 abc
2 abc

Is This Answer Correct ?

2 Yes

2 No

If the source has duplicate records as id and name columns, values: 1 a, 1 b, 1 c, 2 a, 2 b, t..

Answer / souvik

We need to have a Lookup with a Dynamic Cache, a Router, an Expression and then an Update Strategy..... The Lookup will be on the Target Table..... So when the ID is coming for the first time, it wont found any data on the lookup table.... This can be directed for Insertion to the Target Table via a Router.... Then when the same ID is coming for the second time, it will Lookup on the Target Table and will found that the row with the ID exists.... Hence both the columns from the source table and both the columns from the Lookup are taken forward into the Router and then to an Expression Transformation where the second port is Concatenated. Then the ID port and this port is taken to the Update Strategy transformation for Update...

Is This Answer Correct ?

1 Yes

2 No

Post New Answer