Get max record of the each ID

[sunilkumarqv]

HI all ,

Please help me to get output table based on inpt table below

Input table

ID,Prodcut,Rowno

1,A,1

1,A,2

2,B,1

3,C,1

4,D,1

4,D,2

4,D,3

5,E,1

Get latest rowno of the each ID below likw this

for

output:

ID,Product,Rowno

1,A,2

2,B,1

3,C,1

4,D,3

5,E,1

[#2]

Hi Sunil,

Can you please try the below script.

input:

load *  inline [

ID, ProductId

1,A

1,A

2,B

3,C

4,D

4,D

4,D

5,E

];

NoConcatenate

output:

load ID, ProductId, if(isnull(Previous(ID))<>0,1, if(ID=Previous(ID),(1+peek("v_flag")),1)) as v_flag

Resident input;

Thanks,

Sreeman

[awhitfield]

Your post stated that you had the RowNo in your input table.

Andy

[sunilkumarqv]

yes your write Andrew

but have to find out the logic for Rowno before that .

[#5]

Hi Sunil,

Can you please check my comment and let me know is it what you are expecting?

Thanks,
Sreeman

[Kushal_Chawda]

Try below to create the Rowno

Data:

Load ID,

         Product

From Table;

new;

load ID,

        Product,

        if(rowno()=1 or ID<>previous(ID) ,1,

        if(ID=previous(ID) and Product=previous(Product),peek('ROWNO')+1,peek('ROWNO'))) as ROWNO

resident Data

order by ID,Product;

drop table Data;