Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead 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

Thanks in advance.

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one example:

QlikCommunity_Thread_282222_Pic1.JPG

QlikCommunity_Thread_282222_Pic2.JPG

QlikCommunity_Thread_282222_Pic3.JPG

QlikCommunity_Thread_282222_Pic4.JPG

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

View solution in original post

2 Replies
Or
MVP
MVP

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

Hi,

one example:

QlikCommunity_Thread_282222_Pic1.JPG

QlikCommunity_Thread_282222_Pic2.JPG

QlikCommunity_Thread_282222_Pic3.JPG

QlikCommunity_Thread_282222_Pic4.JPG

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