topic Re: Get range of values from sequence in QlikView

Get range of values from sequence

markgraham123 — Thu, 11 Aug 2016 20:10:16 GMT

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

Re: Get range of values from sequence

Thu, 11 Aug 2016 20:13:26 GMT

FOLOW

Re: Get range of values from sequence

markgraham123 — Thu, 11 Aug 2016 20:14:20 GMT

Hi,

I just updated the post.

Min Max doesn't work in my scenario.

Re: Get range of values from sequence

Thu, 11 Aug 2016 20:16:06 GMT

More details?

Re: Get range of values from sequence

markgraham123 — Thu, 11 Aug 2016 20:16:43 GMT

@rodrigo,

Please see the updated post and attachment.

Re: Get range of values from sequence

Thu, 11 Aug 2016 20:18:41 GMT

what is the paramether for range?

Re: Get range of values from sequence

markgraham123 — Thu, 11 Aug 2016 20:22:51 GMT

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

Thu, 11 Aug 2016 20:28:10 GMT

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

markgraham123 — Thu, 11 Aug 2016 20:30:22 GMT

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

Thu, 11 Aug 2016 20:37:04 GMT

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

sunny_talwar — Thu, 11 Aug 2016 20:40:20 GMT

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

markgraham123 — Thu, 11 Aug 2016 20:41:32 GMT

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

sunny_talwar — Thu, 11 Aug 2016 20:42:46 GMT

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

];

Re: Get range of values from sequence

sunny_talwar — Thu, 11 Aug 2016 20:44:32 GMT

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;

Re: Get range of values from sequence

markgraham123 — Thu, 11 Aug 2016 20:46:40 GMT

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

sunny_talwar — Thu, 11 Aug 2016 20:49:02 GMT

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

];

Re: Get range of values from sequence

sunny_talwar — Thu, 11 Aug 2016 20:50:02 GMT

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

Re: Get range of values from sequence

markgraham123 — Thu, 11 Aug 2016 20:51:17 GMT

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

sunny_talwar — Thu, 11 Aug 2016 21:39:29 GMT

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

markgraham123 — Thu, 11 Aug 2016 21:48:17 GMT

You nailed it in a class bro.

Learnt great thing.

Thanks a lot sunny .