Concatenation of multiple values

sujaoschin · Post by **sujaoschin** » Sun Sep 14, 2008 6:12 pm

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.

ray.wurlod · Post by **ray.wurlod** » Sun Sep 14, 2008 7:51 pm

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.

sujaoschin · Post by **sujaoschin** » Sun Sep 14, 2008 11:02 pm

There is only one reference key.

ray.wurlod · Post by **ray.wurlod** » Sun Sep 14, 2008 11:05 pm

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!

sujaoschin · Post by **sujaoschin** » Mon Sep 15, 2008 12:55 am

Sorry.Mistyped. '1' should not be there.

It is like this
Lookup
--------
MT_MODELNUMBER MT_ID
---------------------- --------
A 101

Input
------
MT_MODELNUMBER MT_REPLACEDBYID
----------------------- -------------------------
1 101
2 101
3 101

Can you plz advise how to proceed ahead?

ray.wurlod · Post by **ray.wurlod** » Mon Sep 15, 2008 1:00 am

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.

sujaoschin · Post by **sujaoschin** » Mon Sep 15, 2008 1:47 am

No. I am looking up '101' from the input with the 101 inthe lookup. i.e
if input. MT_REPLACEDBYID =lookup.MT_ID then I should get A, 1,2,3.

But I am getting only A,3 as the output.

ray.wurlod · Post by **ray.wurlod** » Mon Sep 15, 2008 2:26 am

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.

sujaoschin · Post by **sujaoschin** » Mon Sep 15, 2008 2:43 am

My expression in transformer is

if Input .MT_REPLACEDBYID=lookup.MT_ID then lookup.MT_MODELNUMBER:',':Input.MT_MODELNUMBER else Input.MT_MODELNUMBER

Plz let me know how to enable multiple rows return.

dsusr · Post by **dsusr** » Mon Sep 15, 2008 4:16 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

sujaoschin · Post by **sujaoschin** » Mon Sep 15, 2008 5:08 pm

Yes. I need your help in stage variable logic. Plz let me know.

dsusr · Post by **dsusr** » Tue Sep 16, 2008 8:58 am

sujaoschin wrote:Yes. I need your help in stage variable logic. Plz let me know.

First sort the input data to transformer on the key MT_ID, MT_REPLACEDBYID......

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

sujaoschin · Post by **sujaoschin** » Mon Sep 22, 2008 3:10 am

Thanks a lot. I did as you have told in this forum. It worked and this problem is resolved.