Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Bucket Movement of Values and Identification

Hi Guys,

Find attached data for reference.

I need to count as follows:

-- Bifurcation of Buckets:

a) New IDs moved in the month from previous

b) Same IDs in the same Bucket

For example:

ID 1000 - Remained constant in Bucket 1-30 throughout

ID 1001 - Moved from 01-30 -> 30-60 in Feb

ID 1002 - Moved from 01-30 -> 30-60 in Mar

ID 1003 - Moved from 01-30 -> 30-60 -> 60-90 from each month

ID 1004 - Remained constant in 01-30

ID 1005 - Moved from 01-30 -> 30-60 in Mar

In the attached excel Tab: Intermediate I have created 2 fields:

1) Earlier Bucket - Represents which was the bucket earlier

2) Flag - Where earlier bucket has changed to higher one is marked as 1

Ideally Flag 1 would give me how many have moved in a higher bucket and zero would mean they are in the same bucket.

Will be great if you guys could provide me some solution on this

1 Solution

Accepted Solutions
MVP

Hi,

one example:

```tabBuckets:
Dual(Replace(Class(Value, 30),'<= x <','-'),Class(Value, 30)) as Bucket;
MakeDate(2017,IterNo()) as Date,
Floor(Rand()*90) as Value
AutoGenerate 10
While IterNo()<=12;

Join
Dual(Pick(Match(1,BucketUp,BucketConst,BucketDown),'Up','Const','Down'),BucketUp-BucketDown) as BucketChange;
-(Bucket>PreviousBucket) as BucketUp,
-(Bucket=PreviousBucket) as BucketConst,
-(Bucket<PreviousBucket) as BucketDown;
Bucket,
Previous(Bucket) as PreviousBucket
Resident tabBuckets
Where ID=Previous(ID)
Order By ID, Date;

Join
Bucket,
PreviousBucket,
Dual(PreviousBucket&' -> '&Bucket,AutoNumberHash128(PreviousBucket,Bucket)) as BucketTransition
Resident tabBuckets
Where IsNum(PreviousBucket)
Order By Bucket, PreviousBucket;

```

hope this helps

regards

Marco

2 Replies
MVP

Getting the first step can be done with the Peek() function:

Data:

Date,

Bucket

FROM

[Case 1.xlsx]

(ooxml, embedded labels, table is [Raw Data]);

Intermediate:

Load ID, Date, Bucket, if(Peek(ID,-1) = ID, Peek(Bucket, -1)) as PrevBucket, if(Peek(ID,-1)=ID,1,0) as HasMovedFlag

Resident Data

ORDER BY ID, Date;

Drop table Data;

I'm not entirely sure what you're trying to get with the desired output part, but it looks like a pivot table with Bucket and PrevBucket as the dimensions, one of which is pivoted to the top (cross table), with count(ID) being the expression.

MVP

Hi,

one example:

```tabBuckets:
Dual(Replace(Class(Value, 30),'<= x <','-'),Class(Value, 30)) as Bucket;
MakeDate(2017,IterNo()) as Date,
Floor(Rand()*90) as Value
AutoGenerate 10
While IterNo()<=12;

Join
Dual(Pick(Match(1,BucketUp,BucketConst,BucketDown),'Up','Const','Down'),BucketUp-BucketDown) as BucketChange;
-(Bucket>PreviousBucket) as BucketUp,
-(Bucket=PreviousBucket) as BucketConst,
-(Bucket<PreviousBucket) as BucketDown;
Bucket,
Previous(Bucket) as PreviousBucket
Resident tabBuckets
Where ID=Previous(ID)
Order By ID, Date;

Join
Bucket,
PreviousBucket,
Dual(PreviousBucket&' -> '&Bucket,AutoNumberHash128(PreviousBucket,Bucket)) as BucketTransition
Resident tabBuckets
Where IsNum(PreviousBucket)
Order By Bucket, PreviousBucket;

```

hope this helps

regards

Marco

Community Browser