Data Manipulation

[Mark_E]

hi, i have 3 fields (materialnum, seqnum, text)

the issue i have is:

1) If two text values in a Sequence number are the same, i need to take only the 1st text value but still pass the other row through but with a blank value for the text column.

from
(materialnum, seqnum, text)
1 01 a
2 01 a

into

1 01 a
2 01

thanks in advance

[Mark_E]

hi i got it working but its just the ordering i.e.

from
(materialnum, seqnum, text)
1 01 a
2 01 a
3 02 s
4 03 q
5 03 q

into

1 01 a
2 01
3 02 s
4 03 q
5 03

[ArndW]

Did you still have a question on how to do this?

[Mark_E]

sorry original problem still exists

from
(materialnum, seqnum, text)
1 01 a
2 01 a
3 01 a
4 02 w
4 02 w
5 03 c

into

1 01 a
2 01
3 01
4 02 w
4 02
5 03 c

[ArndW]

Make sure the data is sorted, then use a Transform stage with a stage variable that store the last row's seqnum. If it is the same as this row's seqnum don't put a value in "text", otherwise do.

[Mark_E]

hi,

yes i do, i have got it working for the following

from
(materialnum, seqnum, text)
1 01 a
2 01 a

into

1 01 a
2 01

but i also need to incorporate concatentaion of text values for the same seq num, as below, im sure it can be done in the stage variables

from
(materialnum, seqnum, text)
1 01 a
2 01 a
3 02 w
4 02 d
5 02 e

into

1 01 a
2 01
3 02 w d e
4 02
5 02

thanks

[Mark_E]

hi,

yes i do, i have got it working for the following

from
(materialnum, seqnum, text)
1 01 a
2 01 a

into

1 01 a
2 01

but i also need to incorporate concatentaion of text values for the same seq num, as below, im sure it can be done in the stage variables

from
(materialnum, seqnum, text)
1 01 a
2 01 a
3 02 w
4 02 d
5 02 e

into

1 01 a
2 01
3 02 w d e
4 02
5 02

thanks

[ArndW]

It can be done with stage variables, but not the way the data is sorted, you would need to sort by materialnum and descending seqnum to make that work.

[Mark_E]

thanks for the advice, i'll give that a go