Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
FOLOW
Hi,
I just updated the post.
Min Max doesn't work in my scenario.
More details?
@rodrigo,
Please see the updated post and attachment.
what is the paramether for range?
The IDs doesn't have specific start and end point. So, Min and MAX Doesn't work.
they might come in between the numbers.
that I understand. But as you need to separate, you do not have a specific parameter? 10 to 10
Sorry Sir.
I was not able to understand.
For every range of numbers for 'Name',
I want to display its starting and Ending number.
if the names are different the min and max functions. In your example does not work for that one name has several ranges.