16 Replies Latest reply: Feb 15, 2014 12:14 PM by Srikanth P

How to get min and max id

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.

• Re: How to get min and max id

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

min(Id)

Max(id)

Hope it helps

• Re: How to get min and max id

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 ?

• Re: How to get min and max id

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

• Re: How to get min and max id

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

 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.

• Re: How to get min and max id

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.

• Re: How to get min and max id

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

• Re: How to get min and max id

Yes, I do.

Use this:

ExtendedData:

IF (previous(Product) = Product,

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

ID) AS Min,

ID AS Max,

Product AS ProductName

RESIDENT RawData;

Ranges:

Min AS RangeMin,

Max(Max) AS RangeMax

RESIDENT ExtendedData

GROUP BY ProductName, Min;

Good luck,

Peter

• Re: How to get min and max id

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

• Re: How to get min and max id

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
• Re: Re: How to get min and max id

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

• Re: How to get min and max id

Thanks Peter for your quick response

• Re: How to get min and max id

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...

• Re: How to get min and max id

Hi,

Try the below script,

1. Lead the below mentioned script

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:

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
]

;

• Re: How to get min and max id

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

• Re: How to get min and max id

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