Compare and Add

[thumsup9]

1 A AA Start
2 B BB R1
3 C CC R2
4 C DD R3
5 A AA Start
6 B BB R1
7 C CC R2
8 A AA Start
9 B BB R1
10 C CC R2
11 D DD R3
12 A AA Start .... So on


I am looking for

1 A,B,c,D,AA,BB,CC,DD
5 A,B,C,AA,BB,CC
8,A,B,C,D,AA,BB,CC,DD

I am working on it yet. I am thinking of taking 'Start' as the base.
Any inputs appreciated.

Thanks,

[thumsup9]

I am looking for

4,A,B,c,D,AA,BB,CC,DD
7, A,B,C,AA,BB,CC
11,A,B,C,D,AA,BB,CC,DD

I am working on it yet. I am thinking of taking 'Start' as the base.
Any inputs appreciated.

Thanks,

[DSguru2B]

Yes, you are headed in the right direction. Use 'Start' as your refresh point to clear out the concatenation of present to the previous. Basically you have to check if present row is different then the previous, if it is concatenate them together. Do it for botht he columns and mark the row right before you see 'Start' again. Pick up that row.
Confused?

[DSguru2B]

You will be needing 5 stage variables. Something like

Code: Select all

Cond1     | if (In.Col4 = 'Start') then In.Col2 else (if In.Col2 <> PrevCol2  
                then Cond1:',':In.Col2 else Cond1)

Cond2     | if (In.Col4 = 'Start') then In.Col3 else (if In.Col3 <> PrevCol3 
                then Cond2:',':In.Col3 else Cond2)

PrevCol2 | In.Col2
PrevCol3 | In.Col3
Id           | If In.Col4 = 'Start' then In.Col1 else Id

These stage variables should be in the exact same order.
The derivation for the first output column will be Id and for the second it will be Cond1:',':Cond2.

This is just the first part. It will build you a temp text file.
For the second part, feed the output of your text file into aggregator and group on first column and give the derivation for your second column as 'Last'. You done.
Not so confused an more huh

[thumsup9]

I am working on the points you mentioned. Thanks Guru, I will let you know once this is fixed.

Thanks,

[DSguru2B]

My logic is based on your last column. I hope you really do have that column and didnt just make that up for our clarification.