concatenate issue on one field

ysrini9 · Post by **ysrini9** » Thu Jun 05, 2008 3:18 am

Hi
my source contains 3 feilds i.e.,Location_key,Seq_no and Text.

I have a requirement based on the following sample input data
Location_key Seq_no Text
000010 1 Special Insp
000010 2 Busa
000010 3 Canar
000010 4 Dat
000011 1 Dus
000012 1 Inventory Supplier
000012 2 Ins Inspect
000012 3 Tero

I need to concatenate the Text field data

The output data should be following:-

Location_key Seq_no Text
000010 1 Special Insp
000010 2 Special Insp:Busa
000010 3 Special Insp:Busa:Canar
000010 4 Special Insp:Busa:Canar:Dat
000011 1 Dus
000012 1 Inventory Supplier
000012 2 Inventory Supplier:Ins Inspect
000012 3 Inventory Supplier:Ins Inspect:Tero

Can anyone please suggest me how to achive this.

ArndW · Post by **ArndW** » Thu Jun 05, 2008 3:51 am

This is easily achieved using a transform stage and stage variables. One stage var is used to see if the current location_key is the same as the last. If it is, then another stage var that contains the Text is modified by appending the new text, if the key isn't the same then this variable is reset to just contain the current text.

ysrini9 · Post by **ysrini9** » Thu Jun 05, 2008 4:22 am

ArndW wrote:This is easily achieved using a transform stage and stage variables. One stage var is used to see if the current location_key is the same as the last. If it is, then another stage var that contains t ...

I tried your logic but it is not working. can you please explain in detail

ysrini9 · Post by **ysrini9** » Thu Jun 05, 2008 4:24 am

ArndW wrote:This is easily achieved using a transform stage and stage variables. One stage var is used to see if the current location_key is the same as the last. If it is, then another stage var that contains t ...

I tried your logic but it is not working. can you please explain in detail

ArndW · Post by **ArndW** » Thu Jun 05, 2008 4:27 am

Tell us what you have tried first.

dsean · Post by **dsean** » Thu Jun 05, 2008 2:42 pm

I don't know whether you really tried or not becuase this is very easy. Please search the forums for more information on stage variables. I am sure you will find thousands of posts. Since I had done this before, here is what you are looking for

svCurr : Src.Col1
svUni : If svCurr = svPre then 'Y' else 'N'
svCurrText : if svUni = 'Y' then svPrevText:Src.Col3 else Src.Col3
svPrevText : if svUni='Y' then svCurrText else Src.Col3
svPre : If Src.Col1 = svPre then svPre else Src.Col1

Thanks
Sean

ysrini9 · Post by **ysrini9** » Thu Jun 05, 2008 10:18 pm

dsean wrote:I don't know whether you really tried or not becuase this is very easy. Please search the forums for more information on stage variables. I am sure you will find thousands of posts. Since I had done this before, here is what you are looking for

svCurr : Src.Col1
svUni : If svCurr = svPre then 'Y' else 'N'
svCurrText : if svUni = 'Y' then svPrevText:Src.Col3 else Src.Col3
svPrevText : if svUni='Y' then svCurrText else Src.Col3
svPre : If Src.Col1 = svPre then svPre else Src.Col1

Thanks
Sean

Thanks for your support.

I used 4 stage variables in Transformer stage i.e.,
s1:Text field
s2:if loc_key<>s4 then s1 else s3:s1
s3:s2
s4:loc_key
Its working good.

Thanks
srini

anbu · Post by **anbu** » Fri Jun 06, 2008 12:42 am

You can do that using two stage variables.

Derivation Stage variables
if lockey = DSLink3.Location_key then txt : ':' : DSLink3.Text else DSLink3.Text txt
DSLink3.Location_key lockey