Bucket Movement of Values and Identification

[Anonymous]

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

Thanks in advance.

[Or]

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

Data:

LOAD ID,

     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.

[MarcoWedel · Accepted solution]

Hi,

one example:

tabBuckets:
LOAD RowNo() as RowNo, *,
     Dual(Replace(Class(Value, 30),'<= x <','-'),Class(Value, 30)) as Bucket;
LOAD 1000+RecNo()-1 as ID,
     MakeDate(2017,IterNo()) as Date,
     Floor(Rand()*90) as Value
AutoGenerate 10
While IterNo()<=12;

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

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

hope this helps

regards

Marco