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.

[alexandros17]

Simply create a chart with product as dimension and add two expressions:

min(Id)

Max(id)

Hope it helps

[manojkulkarni]

From the data you provided, you will get only 2 rows

Product A  Min is 3 & Max is 16

Product B Min is 20 & Max is 27.

Do you have another requirement or condition to arrive the required results ?

[luis_pimentel]

Create a chart, with product as dimension an min(ID) and max(ID) as expressions

[Anonymous]

I want to get this:

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

instead of this:

Min	Max	Product
3	16	ProductA
20	27	ProductB

because the Id 8, 13 might belong to other product not ProductA and Id 22,24 not belong to ProductB as well.

[sujeetsingh]

you can do this in two ways :

1-In script by using Group By

2-In Ui by using the Dimension product and finding MIN & MAX

The one i have done in UI see the sample

[Peter_Cammaert]

Yes, I do.

Use this:

ExtendedData:
LOAD ID,

     IF (previous(Product) = Product,

        IF (previous(ID) = ID-1, peek(Min), ID),

        ID) AS Min,

     ID AS Max,

     Product AS ProductName

RESIDENT RawData;



Ranges:

LOAD ProductName,

     Min AS RangeMin,

     Max(Max) AS RangeMax

RESIDENT ExtendedData

GROUP BY ProductName, Min; 

See also document below. Use table viewer to inspect table Ranges.

Good luck,

Peter

[MK_QSL]

Looks like the dimensions you have provided in not enough to get the desired result.

You might be missing the third dimension.

I have assumed the third dimension and created this document. Please check...

[Peter_Cammaert]

Sorry, my script code window in Advanced Editor is going haywire. Had to correct the mess a bit. See the QVW for a working script.

Peter

[alexandros17]

I need more information about grouping logic ...