Concatinate String

[rcil]

Hi

Which is the best way to achieve this in a hash file

Code: Select all

Input: 

COL1,COL2,COL3,COL4,COL5
A,B,C,D,1
A,B,C,D,2
A,B,C,D,3
E,F,G,H,10
E,F,G,H,20
E,F,G,H,30

OutPut:

Last column separated by any delimiter ex:char(250)
A,B,C,D,1|2|3
E,F,G,H,10|20|30 

The key columns in my source are col3 and col4. I created a empty hash file with the col3 and col4 as keys and a sepate column which can handle the string length. In a separate job I am doing a lookup with empty hash and writing to it. I think I am doing something wrong in my stage variables which results as this for couple of rows
A,B,C,D,1|2|3|1|2 and some of them are good.

I am not sure whether my approch is correct or not but is there any better way of doing this?

thanks

[kcbland]

If you're using stage variables, you have to have one last row in your input source cause the final group to output.

Your job would be easier if you reference the same hash file you are writing. Simply reference the row and concatentate the incoming values onto the referenced row. Then, output the row back to the hash file. Every subsequent lookup for the same row will bring back the ever growing concatenated row. Disable all read and write caching. You won't use stage variables at all, plus you won't have the issue with the last group like you will have with stage variables. When you are done, the hash file contains your data, simply output to to a file if that's how you need it.

[rcil]

Simply reference the row and concatentate the incoming values onto the referenced row.

I was refrencing the same hash file and writing to it but I was using stage variables. I will try the way you suggested but can I know what should be my derivation in my last column where I have to concatenate the strings.


thanks

[rcil]

Simply reference the row and concatentate the incoming values onto the referenced row.

I was refrencing the same hash file and writing to it but I was using stage variables. I will try the way suggested but can I know what should be my derivation in my last column where I have to concatenate the strings.

My input is

A,B,C,D,8
A,B,C,D,10
E,F,G,H,8
E,F,G,H,10
I,J,K,L,6
I,J,K,L,8
I,J,K,L,10

I used my derivations as

Code: Select all

If IsNull(RefLink.Col5) Then InpLink.Col5 Else RefLink.Col5 : "|" : InpLink.Col5

the output I got is

I,J,K,L,10|6|8
A,B,C,D,8|10|8|8
E,F,G,H,8|10|10

The second and third rows are concatinated twice. Is there any solution for this. I mean any changes in my derivation will solve the problem?

thanks

[kcbland]

You've probably screwed up your key metadata on the hash file. Check that, because it MUST work that way described if the hash metadata is correct.

[Sainath.Srinivasan]

Sort input file with first 4 key cols.
Use stage variable to concatenate description of rows - if prev set of keys = curr set of keys. Otherwise only current description.
Write keys and description into an hash file with the 4 cols as keys.
Each identical key will overwrite the other to form the full record you want.

[rcil]

There is nothing wrong with the metadata. It is same as the source metadata from the database. I made only col3 and col4 as keys in my hash file which are the keys from the database.

Do I have to change the metadata of my last column in which I am going to store the string? Any thoughts?