Page 1 of 1

concatenate issue on one field

Posted: Thu Jun 05, 2008 3:18 am
by ysrini9
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.

Posted: Thu Jun 05, 2008 3:51 am
by ArndW
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.

Posted: Thu Jun 05, 2008 4:22 am
by ysrini9
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

Posted: Thu Jun 05, 2008 4:24 am
by ysrini9
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

Posted: Thu Jun 05, 2008 4:27 am
by ArndW
Tell us what you have tried first.

Posted: Thu Jun 05, 2008 2:42 pm
by dsean
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

Posted: Thu Jun 05, 2008 10:18 pm
by ysrini9
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

Posted: Fri Jun 06, 2008 12:42 am
by anbu
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