Concatenation of multiple values
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 102
- Joined: Tue Jan 31, 2006 4:13 am
Concatenation of multiple values
Input
-------
MT_MODELNUMBER MT_REPLACEDBYID
1 101
2 101
3 101
LOOKUP table
----------
MT_MODELNUMBER MT_ID
A 1 101
Output field - BISEPS_MODELNUM should have values obeying this condition
[ condition for look up is 'if INPUT.MT_REPLACEDBYID=LOOKUP.MT_ID THEN CONCATENATE lookup.MT_MODELNUMBER , input.MT_MODELNUMBER VALUES, input.MT_MODELNUMBER OLD VALUES)
the output should be like ( SEPARATED BY COMMAS)==>A,1,2,3
I am able to concatenate only two values using the below condition
'if Input .MT_REPLACEDBYID=lookup.MT_ID then lookup.MT_MODELNUMBER:',':Input.MT_MODELNUMBER else Input.MT_MODELNUMBER'==> RESULT i AM GETTING IS A,1.
How to concatenate the 2 and 3 to the net result.
-------
MT_MODELNUMBER MT_REPLACEDBYID
1 101
2 101
3 101
LOOKUP table
----------
MT_MODELNUMBER MT_ID
A 1 101
Output field - BISEPS_MODELNUM should have values obeying this condition
[ condition for look up is 'if INPUT.MT_REPLACEDBYID=LOOKUP.MT_ID THEN CONCATENATE lookup.MT_MODELNUMBER , input.MT_MODELNUMBER VALUES, input.MT_MODELNUMBER OLD VALUES)
the output should be like ( SEPARATED BY COMMAS)==>A,1,2,3
I am able to concatenate only two values using the below condition
'if Input .MT_REPLACEDBYID=lookup.MT_ID then lookup.MT_MODELNUMBER:',':Input.MT_MODELNUMBER else Input.MT_MODELNUMBER'==> RESULT i AM GETTING IS A,1.
How to concatenate the 2 and 3 to the net result.
Sujatha K
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This looks like a singularly cumbersome design.
How many lookup keys do you have? What are the reference key expressions for it/these?
Combined with the result of whether or not the lookup was successful you should be able to generate the required output as an output column derivation in the Transformer stage.
THEN you need to perform a vertical pivot. Search the forum for "vertical pivot". There are at least three techniques depending on whether the number of pivot rows is constant/variable and large/small.
How many lookup keys do you have? What are the reference key expressions for it/these?
Combined with the result of whether or not the lookup was successful you should be able to generate the required output as an output column derivation in the Transformer stage.
THEN you need to perform a vertical pivot. Search the forum for "vertical pivot". There are at least three techniques depending on whether the number of pivot rows is constant/variable and large/small.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 102
- Joined: Tue Jan 31, 2006 4:13 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Yes? What is it? The constant "A", the value of MT_MODELNUMBER, or the concatenation of both and, if so, with a space between or not? You see, in your specification you provide two column headings and three values. If you're that confused imagine how much worse it is for someone else trying to understand your design specification!
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 102
- Joined: Tue Jan 31, 2006 4:13 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
OK, you look up using "A" as the reference key expression. You get back 101. You compare this ROW BY ROW to determine whether there is a match. You can NOT get a single row output from this. Downstream of the Transformer stage you need to perform a "vertical pivot".
Search the forum for "vertical pivot". There are at least three techniques depending on whether the number of pivot rows is constant/variable and large/small.
Search the forum for "vertical pivot". There are at least three techniques depending on whether the number of pivot rows is constant/variable and large/small.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 102
- Joined: Tue Jan 31, 2006 4:13 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Yes, IN THREE ROWS. And then only if you've enabled multi-row return.
Please post your reference key expression - copy it from the transformer stage and paste it here.
Please post your reference key expression - copy it from the transformer stage and paste it here.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 102
- Joined: Tue Jan 31, 2006 4:13 am
You have o write the logic within the transformer to read the previous values.
Do a lookup and you will get the following output from Lookup:
MT_MODELNUMBER, MT_ID , MT_REPLACEDBYID
A 101 1
A 101 2
A 101 3
Now you have to define the Logic using Stage variables in the transformer to merge these 3 records together. Output from Transformer should be:-
MT_MODELNUMBER, MT_ID , MT_REPLACEDBYID, NewColumn
A 101 1 A,1
A 101 2 A,1,2
A 101 3 A,1,2,3
Sort and Remove Duplicate on the keys to pick the last record.
Let us know if you need help on the Stage Variables logic.
Regards,
dsusr
Do a lookup and you will get the following output from Lookup:
MT_MODELNUMBER, MT_ID , MT_REPLACEDBYID
A 101 1
A 101 2
A 101 3
Now you have to define the Logic using Stage variables in the transformer to merge these 3 records together. Output from Transformer should be:-
MT_MODELNUMBER, MT_ID , MT_REPLACEDBYID, NewColumn
A 101 1 A,1
A 101 2 A,1,2
A 101 3 A,1,2,3
Sort and Remove Duplicate on the keys to pick the last record.
Let us know if you need help on the Stage Variables logic.
Regards,
dsusr
-
- Premium Member
- Posts: 102
- Joined: Tue Jan 31, 2006 4:13 am
First sort the input data to transformer on the key MT_ID, MT_REPLACEDBYID......sujaoschin wrote:Yes. I need your help in stage variable logic. Plz let me know.
In the transformer define the following stage variables
STGCount ----> If InputCol.MT_ID <> STGPrevious Then '1' Else STGCount + 1
STGConcat ----> If STGCount = '1' Then InputCol.MT_REPLACEDBYID Else STGConcat : ',' InputCol.MT_REPLACEDBYID
STGPrevious -----> InputCol.MT_ID
Defined the below Columns in the Output Link of Transformer
OutCol.MT_ID = InputCol.MT_ID
OutCol.MT_REPLACEDBYID = InputCol.MT_MODELNUMBER : STGConcat
OutCol.Count = STGCount
Now sort and do the remove the duplicate to pick the last record based on count for each MT_ID
-
- Premium Member
- Posts: 102
- Joined: Tue Jan 31, 2006 4:13 am