Concatenate Records?

[welby]

Hi I have a set of records that I want to concatenate where a particular field is matching, such as a security Number and then output a concatenated text file.

Is this possible, if so how? I'm stumped!

Thank you

[bcooney]

Not quite clear, but I interpret what you say as this: "I would like to find all the records in the table that have the same security number in the security number field. I would then like to export those records to a text file."

If I've correctly stated your goal, you would:

1. Use Find Mode to enter the desired security number.

2. Then you Export Records to generate a text file, selecting the fields that you require.

[welby]

Hi,

thank you for the prompt reply, but what I want is that where the security number is the same I wish to Concatenate the output TXT file as:

RECORD1,RECORD2,RECORD3[newline]

RECORD4[newline]

RECORD5[newline]

RECORD1, RECORD2, RECORD3 will have the same security number, while RECORD4 AND RECORD5 will have unique security numbers.

I hope this makes more sense.

Thanks

Mark

Edited September 29, 2007 by Guest

[bcooney]

Maybe someone else has an idea.

[Lee Smith]

I think I'm understanding, but if we can see an example, we might be able to tailor our response for your needs.

On the surface, this sounds like a Summary Fields, and Subsummary parts / Report by Security numbers.

Lee

Edited September 29, 2007 by Guest

[welby]

Hi,

in essence the fixed width record will be like:

Line 1:NAME|SECURITY NO 1|DETAILS|ETC

Line 2:NAME|SECURITY NO 1|DETAILS|ETC

Line 3:NAME|SECURITY NO 1|DETAILS|ETC

Line 4:NAME|SECURITY NO 2|DETAILS|ETC

Line 5:NAME|SECURITY NO 3|DETAILS|ETC

I have just used pipes to seperate the fields and they do not appear in the data as the records are fixed with and NOT CSV.

What I need to end up with an output file, whereby if the field SECURITY NO is duplicated the duplicated records need to be concatenated onto the first one and so on and so on....

Line 1:NAME|SECURITY NO 1|DETAILS|ETC|Line 2:|NAME|SECURITY NO 1|DETAILS|ETC|Line 3:|NAME|SECURITY NO1|DETAILS|ETC|

Line 4:NAME|SECURITY NO 2|DETAILS|ETC|

Line 5:NAME|SECURITY NO 3|DETAILS|ETC|

I can't think of an easier way of describing the issue. Someone mentioned to me that maybe a processor like awk might be able to do it, but I don't even know what awk is and have no expereince of it.

Thanks

[Lee Smith]

For your simplistic response to show an example of the actual data, I will give you a simple response.

Read up on Relationships, Finds, and Summary Fields, and Sub-Summary Parts, I think your answer lies in one of these.

Based on the snip-let of data given, you could do this with:

A. Relationship, based on the Security Number, and view the data through a Portal.

B. Create a list view, and do a Find based on the security number.

C. Create a Report and use Sub-Summary part.

When I said an example of the text, I wanted to see it in its RAW form, not after you had done anything to it. If it is comma delimated, then show it that way, including any other separaters. Otherwise, you are only getting a partal answer to your total problem.

AND, I have not idea what [color:red] awk refers to. But if someone recommended it, ask them.

Lee

Edited September 30, 2007 by Guest

[bcooney]

Like Lee says, it's frustrating to be given a narrow window to look thru when seeing the big picture may help.

Line 1:NAME|SECURITY NO 1|DETAILS|ETC|Line 2:|NAME|SECURITY NO 1|DETAILS|ETC|Line 3:|NAME|SECURITY NO1|DETAILS|ETC|

Line 4:NAME|SECURITY NO 2|DETAILS|ETC|

Line 5:NAME|SECURITY NO 3|DETAILS|ETC|

However, since you are focusing on an text output file, I would also look into a looping script that writes to a table devoted to holding the records for output.

Each line that you describe above is a record. Therefore, the looping script will process your existing table and do something like create a new record in the output table if the security number changed, or add to the existing record (append to the output table security number text field) if the security number matches the previous value. Then you'd export the output table's name field, the security no field, and details, etc.

[welby]

Hi,

I'm sorry I cannot post "live data" as I am governed by the "Data Protection Act" in the UK.

I have tried to describe the problem that I have with which I thought best represented the data without over-complicating the issue.

I can see where you are coming from and will give it a try.

Thank you. You have been most helpful and very constructive with your answer.

Regards

Mark