Need help to get count in Script

[santoshrai]

hi,

Please help me sort this,

i want to get the column in script which will show nos of amendment per material, please guide me to do it.

Data base

Amendment no          po number     material

1121                         55221100     abc123

1122                         55221100     abc123

1123                         55221100     abc123

1234                         55331100     xyz123

2132                         45421121     pqr234

2133                         45421121     pqr234

2134                         45421121     pqr234

2135                         45421121     pqr234

2136                         45421121     pqr234

What i want column in script to get count of nos of amendment per material

Amendment no          po number     material     Count

1121                         55221100     abc123           1

1122                         55221100     abc123           1

1123                         55221100     abc123           2

1234                         55331100     xyz123           1

2132                         45421121     pqr234            1

2133                         45421121     pqr234             2

2134                         45421121     pqr234             3

2135                         45421121     pqr234             4

2136                         45421121     pqr234             5

Thanks in advance.

[Chanty4u]

hi,

load

*,

coun(distinct Amendment no )  as new count;

.....

from ur data source;

[sunny_talwar]

Not sure how you are getting these numbers?

Should it be 1, 2, 3 instead of 1, 1, 2?

[sunny_talwar]

If what I pointed out makes sense, then you can try this script:

Table:

LOAD * Inline [

Amendment no,          po number,    material

1121,                        55221100,    abc123

1122,                        55221100,    abc123

1123,                        55221100,    abc123

1234,                        55331100,    xyz123

2132,                        45421121,    pqr234

2133,                        45421121,    pqr234

2134,                        45421121,    pqr234

2135,                        45421121,    pqr234

2136,                        45421121,    pqr234

];

FinalTable:

LOAD *,

  If(Peek('material') = material, RangeSum(Peek('Count'), 1), 1) as Count

Resident Table

Order By material, [Amendment no];

DROP Table Table;

[Kushal_Chawda]

Data:

Load  Amendment_no,

            po_no,

            material

From Table;

New:

noconcatenate

load *,

        if(rowno()=1 or material<>previous(material),1,peek('Count')+1) as Count

resident Data

order by material;

drop table Data;

[santoshrai]

Thanks buddy, rightly spotted out by you...

you solution work fine....Many many thanks.

[santoshrai]

Thanks buddy...

Your solution is too good and worked fine for me...

Many Many thanks...