20 Replies Latest reply: Aug 11, 2016 5:51 PM by Sunny Talwar

# Get range of values from sequence

Hi all,

I have following data:

Name, ID

A,1

A,2

A,3

A,4

A,5

B,6

B,7

B,8

B,9

A,98

A,99

B,101

B,121

I want to make it as a range:

O/P:

Name  Start  End

A            1     5

B            6     9

A            98   99

B            101  121

Any help is highly appreciated

FOLOW

• ###### Re: Get range of values from sequence

Hi,

I just updated the post.

Min Max doesn't work in my scenario.

• ###### Re: Get range of values from sequence

More details?

• ###### Re: Get range of values from sequence

@rodrigo,

Please see the updated post and attachment.

• ###### Re: Get range of values from sequence

what is the paramether for range?

• ###### Re: Get range of values from sequence

The IDs doesn't have specific start and end point. So, Min and MAX Doesn't work.

they might come in between the numbers.

• ###### Re: Get range of values from sequence

that I understand. But as you need to separate, you do not have a specific parameter? 10 to 10

• ###### Re: Get range of values from sequence

Sorry Sir.

I was not able to understand.

For every range of numbers for 'Name',

I want to display its starting and Ending number.

• ###### Re: Get range of values from sequence

if the names are different the min and max functions. In your example does not work for that one name has several ranges.

• ###### Re: Get range of values from sequence

How did you come up with this for A?

Name  Start  End

A            1     5

A            98   99

• ###### Re: Get range of values from sequence

Coz in the Input data... We have,

A,1

A,2

A,3

A,4

A,5

B,6

B,7

B,8

B,9

A,98

A,99

• ###### Re: Get range of values from sequence

This is the data you have posted in your sample application. Sorting is different

[

Name, ID

A,1

A,2

A,3

A,4

A,5

A,98

A,99

B,6

B,7

B,8

B,9

B,101

B,102

B,103

B,104

B,105

B,106

B,107

B,108

B,109

B,110

B,111

B,112

B,113

B,114

B,115

B,116

B,117

B,118

B,119

B,120

B,121

];

• ###### Re: Get range of values from sequence

May be try this:

Region:

If(Previous(Name) = Name, Alt(Peek('Key'), 1), RangeSum(Peek('Key'), 1)) as Key;

[

Name, ID

A,1

A,2

A,3

A,4

A,5

B,6

B,7

B,8

B,9

A,98

A,99

B,101

B,121

];

TempRegion:

Key,

Min(ID) as Start,

Max(ID) as End

Resident Region

Group By Name, Key;

DROP Table Region;

• ###### Re: Get range of values from sequence

Brother,

I really want to know what is this doing..

If(Previous(Name) = Name, Alt(Peek('Key'), 1), RangeSum(Peek('Key'), 1)) as Key;

You take us through adventure trip every time

• ###### Re: Get range of values from sequence

Assigning each continuous occurrence of Name as a single id

Script:

Region:

If(Previous(Name) = Name, Alt(Peek('Key'), 1), RangeSum(Peek('Key'), 1)) as Key;

[

Name, ID

A,1

A,2

A,3

A,4

A,5

B,6

B,7

B,8

B,9

A,98

A,99

B,101

B,121

];

• ###### Re: Get range of values from sequence

Sunny,

I'm confused at Alt Peek and Range sum.

Cna you please explain.. as i'm learning now

If(Previous(Name) = Name, Alt(Peek('Key'), 1), RangeSum(Peek('Key'), 1)) as Key;

• ###### Re: Get range of values from sequence

Previous Name is checking if the previous value is the same name or not. If it is the same then pick the value of Key from the last row. If previous name is different add 1 to the previous Key. The expression can be simplified to this, I think:

If(Previous(Name) = Name, Peek('Key'), RangeSum(Peek('Key'), 1)) as Key;

• ###### Re: Get range of values from sequence

You nailed it in a class bro.

Learnt great thing.

Thanks a lot sunny .

• ###### Re: Get range of values from sequence

No problem brother

• ###### Re: Get range of values from sequence

That is why the sort is important here, else you will not get the output you are looking for Or may be sort ascending by ID