How to get min and max id

[Anonymous]

Hello Community,

I want to get the min id and max id for the product. I have data like below:

ID	Product
3	ProductA
4	ProductA
5	ProductA
6	ProductA
7	ProductA
9	ProductA
10	ProductA
11	ProductA
12	ProductA
14	ProductA
15	ProductA
16	ProductA
20	ProductB
21	ProductB
23	ProductB
25	ProductB
26	ProductB
27	ProductB

This is what i want to get:

Min	Max	Product
3	7	ProductA
9	12	ProductA
14	16	ProductA
20	21	ProductB
23	23	ProductB
25	27	ProductB

Anyone got idea how to get this? Thanks in advance.

[#2]

With provided information you get only two values. Please add another dimension and use the group by clause in the script to get the aggregated data in the script.

[kumarnatarajan]

Hi,

Try the below script,

1. Lead the below mentioned script

2. add chart (stright table)

    Dimention: Product and ID1 

    Experesion : Max(ID) and Min(ID)

3. (Hide ID1 Column) go to chart properties _> prasentation -> click ID1 column and check hide colimn.

Script:

LOAD

ID,Product,if(len(trim(Peek(ID)))=0,ID,if((ID-Peek(ID))=1,Peek(ID1),ID)) as ID1

INLINE

[
ID, Product
3, ProductA
4, ProductA
5, ProductA
6, ProductA
7, ProductA
9, ProductA
10, ProductA
11, ProductA
12, ProductA
14, ProductA
15, ProductA
16, ProductA
20, ProductB
21, ProductB
23, ProductB
25, ProductB
26, ProductB
27, ProductB
]

;

[Anonymous]

Thanks Peter.

But what if there are subgroup in my data:

ID	Product
3	ProductA
4	ProductA
5	ProductA
6	ProductA
7	ProductA
9	ProductA
10	ProductA
11	ProductA
12	ProductA
14	ProductA
15	ProductA
16	ProductA
20	ProductB
21	ProductB
23	ProductB
25	ProductB
26	ProductB
27	ProductB
3	ProductA1
4	ProductA2
5	ProductA3
6	ProductA4
7	ProductA5
9	ProductA6
10	ProductA7
11	ProductA8
12	ProductA9
14	ProductA10
15	ProductA11
16	ProductA12
20	ProductB1
21	ProductB2
23	ProductB3
25	ProductB4
26	ProductB5
27	ProductB6

and i expect something like this?

Min	Max	Product
3	7	ProductA
9	12	ProductA
14	16	ProductA
20	21	ProductB
23	23	ProductB
25	27	ProductB
3	3	ProductA1
4	4	ProductA2
5	5	ProductA3
6	6	ProductA4
7	7	ProductA5
9	9	ProductA6
10	10	ProductA7
11	11	ProductA8
12	12	ProductA9
14	14	ProductA10
15	15	ProductA11
16	16	ProductA12
20	20	ProductB1
21	21	ProductB2
23	23	ProductB3
25	25	ProductB4
26	26	ProductB5
27	27	ProductB6

[Anonymous]

Thanks all for your reply. But max(ID) and Min(ID) won't return what i want. There are only 2 columns in my table which are Product and ID, there is no 3rd column/dimension

[Peter_Cammaert · Accepted solution]

Well, in my example I assumed the table would be ordered by unique ID. If this isn't the case, then order your table by product first, and then by ID.  The code will still work. In the below example I only changed the input data.

Good luck,

Peter

[Anonymous]

Thanks Peter for your quick response

[#8]

How do you know the ProductA got changed at ID value 8 ?