Assign Value if duplicate record

[Cherukuri]

Hi,

Please find question below:

Input data:

PlateNo ItemNo ItemID GroupId SeqNo
LS10 AB 25 251 1
LS10 AB 25 251 2
LS5 CD 27 253 1
LS5 CD 27 253 2

Should populate output as :

PlateNo ItemNo ItemID GroupId SeqNo IDCL
LS10 AB 25 251 1 2
LS5 CD 27 253 1 2

if the values for the fileds PlateNo,ItemNo,ItemID,GroupId,SeqNo are same then IDCL new column should create
and the 2nd dublicate record seqNo value should be populate in IDCL.

Please can any one help me on this.

Regards,
Cheru.

[ray.wurlod]

This is a very simple Pivot, based on the first three columns as pivot key.

[Cherukuri]

Could you please explain more..Thank you.

[ArndW]

If you look at this link it describes how to make a vertical or horizontal pivot. Once you read that and have tried to make the pivot work and still have a question then we should continue here.

[bhasds]

Hi Cherukuri,

If you are still looking for any logic which can be used in transformer then the below two should give you desired out put-

ONE.

step1-read those 5 columns in a single column in seq file stage and passs to the transformer

step2-take two stage variable in transformer-

Code: Select all

If Trim(colname,Field(colname," ",Dcount(colname," ")),"T") <> SV2 Then Colname Else SV1 :" ":Field(colname," ",Dcount(colname," "))                                                               SV1

Trim(colname,Field(colname," ",Dcount(colname," ")),"T")        SV2(initialise with "")

stpe3- in derivationconstraint-

Code: Select all

Dcount(SV1," ") > Dcount(DSLink105.col1," ")

[chulett]

Or you could just pivot it as this one is as simple as they get so why complicate it?

[bhasds]

TWO

step1- same as ONE
step2-

Code: Select all

If Trim(colname,Right(DSLink105.col1,1),"T") <> SV2 Then colname Else SV1 :" ":Right(colname,1)                SV1

Trim(colname,Right(colname,1),"T")            SV2

step3- Same as ONE.

However, as said by Craig you can still do it through Pivot enterprise stage.