topic Re: How to get min and max id in QlikView

How to get min and max id

Anonymous — Fri, 14 Feb 2014 08:29:11 GMT

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

alexandros17 — Fri, 14 Feb 2014 08:38:16 GMT

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

manojkulkarni — Fri, 14 Feb 2014 08:45:35 GMT

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

luis_pimentel — Fri, 14 Feb 2014 08:45:58 GMT

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

Re: How to get min and max id

Anonymous — Fri, 14 Feb 2014 08:52:45 GMT

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.

Re: How to get min and max id

sujeetsingh — Fri, 14 Feb 2014 08:53:53 GMT

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

Peter_Cammaert — Fri, 14 Feb 2014 08:58:42 GMT

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

Re: How to get min and max id

MK_QSL — Fri, 14 Feb 2014 09:05:50 GMT

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

Peter_Cammaert — Fri, 14 Feb 2014 09:10:14 GMT

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

alexandros17 — Fri, 14 Feb 2014 09:16:56 GMT

I need more information about grouping logic ...

Re: How to get min and max id

Fri, 14 Feb 2014 13:16:11 GMT

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

kumarnatarajan — Fri, 14 Feb 2014 14:14:41 GMT

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
]

;

Re: How to get min and max id

Anonymous — Sat, 15 Feb 2014 13:55:39 GMT

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

Anonymous — Sat, 15 Feb 2014 13:59:58 GMT

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

Peter_Cammaert — Sat, 15 Feb 2014 14:21:50 GMT

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

Anonymous — Sat, 15 Feb 2014 14:57:57 GMT

Thanks Peter for your quick response

Re: How to get min and max id

Sat, 15 Feb 2014 17:14:09 GMT

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