Get range of values from sequence

[markgraham123]

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

[sunny_talwar]

How did you come up with this for A?

Name  Start  End

A            1     5

A            98   99

[markgraham123]

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

[sunny_talwar]

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

LOAD * Inline

[

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

];

[sunny_talwar · Accepted solution]

May be try this:

Region:

LOAD *,

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

LOAD * Inline

[

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:

LOAD Name,

  Key,

  Min(ID) as Start,

  Max(ID) as End

Resident Region

Group By Name, Key;

DROP Table Region;

[markgraham123]

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

[sunny_talwar]

Assigning each continuous occurrence of Name as a single id

Script:

Region:

LOAD *,

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

LOAD * Inline

[

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

];

[sunny_talwar]

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

[markgraham123]

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;

[sunny_talwar]

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;

[markgraham123]

You nailed it in a class bro.

Learnt great thing.

Thanks a lot sunny .