Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

markgraham123
Valued Contributor

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

1 Solution

Accepted Solutions

Re: Get range of values from sequence

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;


Capture.PNG

20 Replies
Not applicable

Re: Get range of values from sequence

FOLOW

markgraham123
Valued Contributor

Re: Get range of values from sequence

Hi,

I just updated the post.

Min Max doesn't work in my scenario.

Not applicable

Re: Get range of values from sequence

More details?

markgraham123
Valued Contributor

Re: Get range of values from sequence

@rodrigo,

Please see the updated post and attachment.

Not applicable

Re: Get range of values from sequence

what is the paramether for range?

markgraham123
Valued Contributor

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.

Not applicable

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

markgraham123
Valued Contributor

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.

Not applicable

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.