concatenate column value of different records in 1 record

[saraswati]

Hi All,

I have a requirement where I have to concatenate the column value of say N records and create one record out of it. For eg in the following sample data of 129 records, I have to combine the column JMTXLN based on the columns JMKCOO,JMDOCO,JMDCTO. This means my output will contain only 4 records(in this case) but the column JMTXLN will be concatenated into one column. So the first 32 records will make a single record and the column values of JMTXLN will be concatenated into single row :

JMKCOO JMDOCO JMDCTO JMLNID JMPNTC JMLINS JMTXLN
00054 1 OO 0 Y 100 ************************************************************
00054 1 OO 0 Y 200 Supplier should not invoice for receipts against this
00054 1 OO 0 Y 300 Blanket Order, Payment for material received will be made
00054 1 OO 0 Y 400 against Release Orders only.
00054 1 OO 0 Y 500 Parts produced for this Blanket Order must comply with the
00054 1 OO 0 Y 600 CompanyX Supplier Quality Standard.
00054 1 OO 0 Y 700 Parts/Material shipped on this Blanket Order must be in
00054 1 OO 0 Y 800 compliance with the CompanyX Supplier and Interplant
00054 1 OO 0 Y 900 Packaging and Handling Guide.
00054 1 OO 0 Y 1000 In the event CompanyX cancels a purchase order or any
00054 1 OO 0 Y 1100 portion thereof for industry non-standard material unique
00054 1 OO 0 Y 1200 to CompanyX requirements, Supplier shall be entitled, subject
00054 1 OO 0 Y 1300 to the maximum stated below, to be reimbursed by CompanyX for
00054 1 OO 0 Y 1400 actual costs incurred by Supplier as a direct result of such
00054 1 OO 0 Y 1500 cancellation which are not recoverable by Supplier by one or
00054 1 OO 0 Y 1600 more of the following:
00054 1 OO 0 Y 1700 a) Supplier's cancellation of material open purchase
00054 1 OO 0 Y 1800 order(s)
00054 1 OO 0 Y 1900 b) Supplier's shipment of the material affected to other
00054 1 OO 0 Y 2000 parties within a reasonable time, or
00054 1 OO 0 Y 2100 c) The exercise by Supplier, in a commercially reasonable
00054 1 OO 0 Y 2200 manner, of all other available mitigation measures.
00054 1 OO 0 Y 2300 Notwithstanding the foregoing, in no event will CompanyX's
00054 1 OO 0 Y 2400 liability for such purchase order cancellations exceed the
00054 1 OO 0 Y 2500 sum of (1) Supplier's then current agreed upon purchase
00054 1 OO 0 Y 2600 price of the SDC quantity recommended by CompanyX based on
00054 1 OO 0 Y 2700 Supplier's response time, plus (2) Supplier's actual cost of
00054 1 OO 0 Y 2800 raw materials and/or parts purchased by Supplier within
00054 1 OO 0 Y 2900 Supplier's established replenishment lead time to meet
00054 1 OO 0 Y 3000 CompanyX requirements as such requirements are set forth
00054 1 OO 0 Y 3100 in the most recent forcast received by Supplier.
00054 1 OO 0 Y 3200 **********************************************************
00054 2 YA 0 Y 100 ************************************************************
00054 2 YA 0 Y 200 Supplier should not invoice for receipts against this
00054 2 YA 0 Y 300 Blanket Order, Payment for material received will be made
00054 2 YA 0 Y 400 against Release Orders only.
00054 2 YA 0 Y 500 Parts produced for this Blanket Order must comply with the
00054 2 YA 0 Y 600 CompanyX Supplier Quality Standard.
00054 2 YA 0 Y 700 Parts/Material shipped on this Blanket Order must be in
00054 2 YA 0 Y 800 compliance with the CompanyX Supplier and Interplant
00054 2 YA 0 Y 900 Packaging and Handling Guide.
00054 2 YA 0 Y 1000 In the event CompanyX cancels a purchase order or any
00054 2 YA 0 Y 1100 portion thereof for industry non-standard material unique
00054 2 YA 0 Y 1200 to CompanyX requirements, Supplier shall be entitled, subject
00054 2 YA 0 Y 1300 to the maximum stated below, to be reimbursed by CompanyX for
00054 2 YA 0 Y 1400 actual costs incurred by Supplier as a direct result of such
00054 2 YA 0 Y 1500 cancellation which are not recoverable by Supplier by one or
00054 2 YA 0 Y 1600 more of the following:
00054 2 YA 0 Y 1700 a) Supplier's cancellation of material open purchase
00054 2 YA 0 Y 1800 order(s)
00054 2 YA 0 Y 1900 b) Supplier's shipment of the material affected to other
00054 2 YA 0 Y 2000 parties within a reasonable time, or
00054 2 YA 0 Y 2100 c) The exercise by Supplier, in a commercially reasonable
00054 2 YA 0 Y 2200 manner, of all other available mitigation measures.
00054 2 YA 0 Y 2300 Notwithstanding the foregoing, in no event will CompanyX's
00054 2 YA 0 Y 2400 liability for such purchase order cancellations exceed the
00054 2 YA 0 Y 2500 sum of (1) Supplier's then current agreed upon purchase
00054 2 YA 0 Y 2600 price of the SDC quantity recommended by CompanyX based on
00054 2 YA 0 Y 2700 Supplier's response time, plus (2) Supplier's actual cost of
00054 2 YA 0 Y 2800 raw materials and/or parts purchased by Supplier within
00054 2 YA 0 Y 2900 Supplier's established replenishment lead time to meet
00054 2 YA 0 Y 3000 CompanyX requirements as such requirements are set forth
00054 2 YA 0 Y 3100 in the most recent forcast received by Supplier.
00054 2 YA 0 Y 3200 **********************************************************
00054 2 YA 0 Y 3400 Key Tronic Corporation must invoice for a specific quantity.
00054 2159 OO 0 Y 100 ************************************************************
00054 2159 OO 0 Y 200 Supplier should not invoice for receipts against this
00054 2159 OO 0 Y 300 Blanket Order, Payment for material received will be made
00054 2159 OO 0 Y 400 against Release Orders only.
00054 2159 OO 0 Y 500 Parts produced for this Blanket Order must comply with the
00054 2159 OO 0 Y 600 CompanyX Supplier Quality Standard.
00054 2159 OO 0 Y 700 Parts/Material shipped on this Blanket Order must be in
00054 2159 OO 0 Y 800 compliance with the CompanyX Supplier and Interplant
00054 2159 OO 0 Y 900 Packaging and Handling Guide.
00054 2159 OO 0 Y 1000 In the event CompanyX cancels a purchase order or any
00054 2159 OO 0 Y 1100 portion thereof for industry non-standard material unique
00054 2159 OO 0 Y 1200 to CompanyX requirements, Supplier shall be entitled, subject
00054 2159 OO 0 Y 1300 to the maximum stated below, to be reimbursed by CompanyX for
00054 2159 OO 0 Y 1400 actual costs incurred by Supplier as a direct result of such
00054 2159 OO 0 Y 1500 cancellation which are not recoverable by Supplier by one or
00054 2159 OO 0 Y 1600 more of the following:
00054 2159 OO 0 Y 1700 a) Supplier's cancellation of material open purchase
00054 2159 OO 0 Y 1800 order(s)
00054 2159 OO 0 Y 1900 b) Supplier's shipment of the material affected to other
00054 2159 OO 0 Y 2000 parties within a reasonable time, or
00054 2159 OO 0 Y 2100 c) The exercise by Supplier, in a commercially reasonable
00054 2159 OO 0 Y 2200 manner, of all other available mitigation measures.
00054 2159 OO 0 Y 2300 Notwithstanding the foregoing, in no event will CompanyX's
00054 2159 OO 0 Y 2400 liability for such purchase order cancellations exceed the
00054 2159 OO 0 Y 2500 sum of (1) Supplier's then current agreed upon purchase
00054 2159 OO 0 Y 2600 price of the SDC quantity recommended by CompanyX based on
00054 2159 OO 0 Y 2700 Supplier's response time, plus (2) Supplier's actual cost of
00054 2159 OO 0 Y 2800 raw materials and/or parts purchased by Supplier within
00054 2159 OO 0 Y 2900 Supplier's established replenishment lead time to meet
00054 2159 OO 0 Y 3000 CompanyX requirements as such requirements are set forth
00054 2159 OO 0 Y 3100 in the most recent forcast received by Supplier.
00054 2159 OO 0 Y 3200 **********************************************************
00054 24000072 OO 0 Y 100 ************************************************************
00054 24000072 OO 0 Y 200 Supplier should not invoice for receipts against this
00054 24000072 OO 0 Y 300 Blanket Order, Payment for material received will be made
00054 24000072 OO 0 Y 400 against Release Orders only.
00054 24000072 OO 0 Y 500 Parts produced for this Blanket Order must comply with the
00054 24000072 OO 0 Y 600 CompanyX Supplier Quality Standard.
00054 24000072 OO 0 Y 700 Parts/Material shipped on this Blanket Order must be in
00054 24000072 OO 0 Y 800 compliance with the CompanyX Supplier and Interplant
00054 24000072 OO 0 Y 900 Packaging and Handling Guide.
00054 24000072 OO 0 Y 1000 In the event CompanyX cancels a purchase order or any
00054 24000072 OO 0 Y 1100 portion thereof for industry non-standard material unique
00054 24000072 OO 0 Y 1200 to CompanyX requirements, Supplier shall be entitled, subject
00054 24000072 OO 0 Y 1300 to the maximum stated below, to be reimbursed by CompanyX for
00054 24000072 OO 0 Y 1400 actual costs incurred by Supplier as a direct result of such
00054 24000072 OO 0 Y 1500 cancellation which are not recoverable by Supplier by one or
00054 24000072 OO 0 Y 1600 more of the following:
00054 24000072 OO 0 Y 1700 a) Supplier's cancellation of material open purchase
00054 24000072 OO 0 Y 1800 order(s)
00054 24000072 OO 0 Y 1900 b) Supplier's shipment of the material affected to other
00054 24000072 OO 0 Y 2000 parties within a reasonable time, or
00054 24000072 OO 0 Y 2100 c) The exercise by Supplier, in a commercially reasonable
00054 24000072 OO 0 Y 2200 manner, of all other available mitigation measures.
00054 24000072 OO 0 Y 2300 Notwithstanding the foregoing, in no event will CompanyX's
00054 24000072 OO 0 Y 2400 liability for such purchase order cancellations exceed the
00054 24000072 OO 0 Y 2500 sum of (1) Supplier's then current agreed upon purchase
00054 24000072 OO 0 Y 2600 price of the SDC quantity recommended by CompanyX based on
00054 24000072 OO 0 Y 2700 Supplier's response time, plus (2) Supplier's actual cost of
00054 24000072 OO 0 Y 2800 raw materials and/or parts purchased by Supplier within
00054 24000072 OO 0 Y 2900 Supplier's established replenishment lead time to meet
00054 24000072 OO 0 Y 3000 CompanyX requirements as such requirements are set forth
00054 24000072 OO 0 Y 3100 in the most recent forcast received by Supplier.
00054 24000072 OO 0 Y 3200 **********************************************************


The output should look like:
00054 1 OO 0 NULL NULL <Conbine_JMTXLN>
00054 2 YA 0 NULL NULL <Conbine_JMTXLN>
00054 2159 OO 0 NULL NULL <Conbine_JMTXLN>
00054 24000072 OO 0 NULL NULL <Conbine_JMTXLN>

Thanks in advance!

If any other information is required please respond.

[ray.wurlod]

Sort your data using a Sort stage and generate a Key Change column. (Better is to use three Sort stages to generate a "last in group" column.)
Accumulate the long string in a stage variable, outputting every row (or, if you have it, when "last in group" occurs). Use a Remove Duplicates stage downstream of that to preserve only the last in each group (not required if you use the "last in group" technique).

[saraswati]

Sort your data using a Sort stage and generate a Key Change column. (Better is to use three Sort stages to generate a "last in group" column.)
Accumulate the long string in a stage variable, outputting every row (or, if you have it, when "last in group" occurs). Use a Remove Duplicates stage downstream of that to preserve only the last in each group (not required if you use the "last in group" technique).

This is not generating the data in the desired way. How to accumulate the column of JMTXLN column into one? Please help on this. The output data based on the data provided should
be as below(There are Four records below, the last column contains the concatenated result of JMTXLN column for all records corresponding to the first 4 columns. As itr can be seen in the data that the first columns are same ):

00054 1 OO 0 ************************************************************
Supplier should not invoice for receipts against this
Blanket Order, Payment for material received will be made
against Release Orders only.
Parts produced for this Blanket Order must comply with the
companyX Supplier Quality Standard.
Parts/Material shipped on this Blanket Order must be in
compliance with the companyX Supplier and Interplant
Packaging and Handling Guide.
In the event companyX cancels a purchase order or any
portion thereof for industry non-standard material unique
to companyX requirements, Supplier shall be entitled, subject
to the maximum stated below, to be reimbursed by companyX for
actual costs incurred by Supplier as a direct result of such
cancellation which are not recoverable by Supplier by one or
more of the following:
a) Supplier's cancellation of material open purchase
order(s)
b) Supplier's shipment of the material affected to other
parties within a reasonable time, or
c) The exercise by Supplier, in a commercially reasonable
manner, of all other available mitigation measures.
Notwithstanding the foregoing, in no event will companyX's
liability for such purchase order cancellations exceed the
sum of (1) Supplier's then current agreed upon purchase
price of the SDC quantity recommended by companyX based on
Supplier's response time, plus (2) Supplier's actual cost of
raw materials and/or parts purchased by Supplier within
Supplier's established replenishment lead time to meet
companyX requirements as such requirements are set forth
in the most recent forcast received by Supplier.
**********************************************************
00054 2 YA 0 ************************************************************
Supplier should not invoice for receipts against this
Blanket Order, Payment for material received will be made
against Release Orders only.
Parts produced for this Blanket Order must comply with the
companyX Supplier Quality Standard.
Parts/Material shipped on this Blanket Order must be in
compliance with the companyX Supplier and Interplant
Packaging and Handling Guide.
In the event companyX cancels a purchase order or any
portion thereof for industry non-standard material unique
to companyX requirements, Supplier shall be entitled, subject
to the maximum stated below, to be reimbursed by companyX for
actual costs incurred by Supplier as a direct result of such
cancellation which are not recoverable by Supplier by one or
more of the following:
a) Supplier's cancellation of material open purchase
order(s)
b) Supplier's shipment of the material affected to other
parties within a reasonable time, or
c) The exercise by Supplier, in a commercially reasonable
manner, of all other available mitigation measures.
Notwithstanding the foregoing, in no event will companyX's
liability for such purchase order cancellations exceed the
sum of (1) Supplier's then current agreed upon purchase
price of the SDC quantity recommended by companyX based on
Supplier's response time, plus (2) Supplier's actual cost of
raw materials and/or parts purchased by Supplier within
Supplier's established replenishment lead time to meet
companyX requirements as such requirements are set forth
in the most recent forcast received by Supplier.
**********************************************************
Key Tronic Corporation must invoice for a specific quantity.
00054 2159 OO 0 ************************************************************
Supplier should not invoice for receipts against this
Blanket Order, Payment for material received will be made
against Release Orders only.
Parts produced for this Blanket Order must comply with the
companyX Supplier Quality Standard.
Parts/Material shipped on this Blanket Order must be in
compliance with the companyX Supplier and Interplant
Packaging and Handling Guide.
In the event companyX cancels a purchase order or any
portion thereof for industry non-standard material unique
to companyX requirements, Supplier shall be entitled, subject
to the maximum stated below, to be reimbursed by companyX for
actual costs incurred by Supplier as a direct result of such
cancellation which are not recoverable by Supplier by one or
more of the following:
a) Supplier's cancellation of material open purchase
order(s)
b) Supplier's shipment of the material affected to other
parties within a reasonable time, or
c) The exercise by Supplier, in a commercially reasonable
manner, of all other available mitigation measures.
Notwithstanding the foregoing, in no event will companyX's
liability for such purchase order cancellations exceed the
sum of (1) Supplier's then current agreed upon purchase
price of the SDC quantity recommended by companyX based on
Supplier's response time, plus (2) Supplier's actual cost of
raw materials and/or parts purchased by Supplier within
Supplier's established replenishment lead time to meet
companyX requirements as such requirements are set forth
in the most recent forcast received by Supplier.
**********************************************************
00054 24000072 OO 0 ************************************************************
Supplier should not invoice for receipts against this
Blanket Order, Payment for material received will be made
against Release Orders only.
Parts produced for this Blanket Order must comply with the
companyX Supplier Quality Standard.
Parts/Material shipped on this Blanket Order must be in
compliance with the companyX Supplier and Interplant
Packaging and Handling Guide.
In the event companyX cancels a purchase order or any
portion thereof for industry non-standard material unique
to companyX requirements, Supplier shall be entitled, subject
to the maximum stated below, to be reimbursed by companyX for
actual costs incurred by Supplier as a direct result of such
cancellation which are not recoverable by Supplier by one or
more of the following:
a) Supplier's cancellation of material open purchase
order(s)
b) Supplier's shipment of the material affected to other
parties within a reasonable time, or
c) The exercise by Supplier, in a commercially reasonable
manner, of all other available mitigation measures.
Notwithstanding the foregoing, in no event will companyX's
liability for such purchase order cancellations exceed the
sum of (1) Supplier's then current agreed upon purchase
price of the SDC quantity recommended by companyX based on
Supplier's response time, plus (2) Supplier's actual cost of
raw materials and/or parts purchased by Supplier within
Supplier's established replenishment lead time to meet
companyX requirements as such requirements are set forth
in the most recent forcast received by Supplier.
**********************************************************

[chulett]

Confusing.

So, you're showing what it should be? Why not show us what you did get? Post that and your transformation logic so we have some clue what is allegedly going wrong. All you've said so far is "it doesn't work" which is singularly unhelpful.

[neena]

Can you try this.

Sort the keys and create following stage variables.

StageVar1: If JMKCOO:JMDOCO:JMDCTO:JMLNID <> Stage Var2 then JMTXLN Else Stage Var3 : JMTXLN

Stage Var2: JMKCOO:JMDOCO:JMDCTO:JMLNID

Stage Var3: Stage Var1

Then assign Stage Var3 to your output column and capture the last row in the group.