topic Re: Create a sequence number and create buckets in QlikView

Create a sequence number and create buckets

markgraham123 — Sat, 16 Nov 2024 03:18:30 GMT

Hi all,

I'm working on a requirement where i have to

1. Sort my data by time and Order

2. Create a sequence number group by Time. An order gets only 1 sequence #.

I'm only able to create sequence # grouped by Time but not able to give just only 1 sequence # to an order.

3. Create buckets of 16 or 8 orders. In my below example data, if i'm not able to create a bucket of 16 or 8, then i give them '99999' value. First priority is to create 16 orders bucket and then 8.

Any help is highly appreciated. Please see attached QVW.

Bucket	Sequence	Order	SKU	Time
1	1	120960148	68397	7
1	2	120961506	85930	7
1	3	159092150	50460	7
1	3	159092150	127908	7
1	4	159095632	47416	7
1	4	159095632	76428	7
1	5	159097798	114205	7
1	6	159110791	52480	7
1	7	159117140	91380	7
1	7	159117140	143758	7
1	8	159119418	76699	7
99999	9	159121210	90575	7
99999	10	159127036	151266	7
1	1	120873063	86881	8
1	2	120879952	84999	8
1	3	120880115	48969	8
1	4	120882274	91722	8
1	5	120911823	113578	8
1	6	120993712	140870	8
1	7	120993718	106345	8
1	8	121021508	86824	8
1	9	121022075	75748	8
1	10	121023970	64721	8
1	11	121030312	140864	8
1	12	121035148	108436	8
1	13	121044204	94053	8
1	14	121063982	60135	8
1	15	121065725	115294	8
1	16	121073529	64682	8
2	17	121112874	143124	8
2	18	121120191	86279	8
2	19	121120811	107735	8
2	20	121125567	130426	8
2	20	121125967	48678	8
2	21	121125986	77085	8
2	22	121126490	69743	8
2	23	159055750	89555	8
2	24	159121194	48101	8
2	24	159121194	51758	8
99999	25	159121557	92789	8
99999	26	159122588	48103	8
99999	27	159123139	144752	8
99999	28	159123777	84839	8

Re: Create a sequence number and create buckets

markgraham123 — Thu, 13 Jun 2019 15:20:49 GMT

@sunny_talwar Any of your help is highly appreciated!

Re: Create a sequence number and create buckets

sunny_talwar — Thu, 13 Jun 2019 17:39:45 GMT

Check attached

Re: Create a sequence number and create buckets

markgraham123 — Thu, 13 Jun 2019 17:54:13 GMT

Sunny,

I first have to check for 16 order bucket. If i donot get 16, then i check for 8. Finally i apply '99999' value for those cant be bucketed in neither 16 nor 8.

Re: Create a sequence number and create buckets

sunny_talwar — Thu, 13 Jun 2019 18:01:26 GMT

What if Time 8 had more than 32 rows? Say 36 rows... what would it look like then?

Re: Create a sequence number and create buckets

markgraham123 — Thu, 13 Jun 2019 18:02:46 GMT

Then it creates 2 - 16 order buckets and rest get '99999'

Re: Create a sequence number and create buckets

sunny_talwar — Thu, 13 Jun 2019 19:00:38 GMT

Not sure if this works always or not, but try this

Re: Create a sequence number and create buckets

sunny_talwar — Thu, 13 Jun 2019 19:06:41 GMT

Actually, this one is better

Re: Create a sequence number and create buckets

markgraham123 — Thu, 13 Jun 2019 19:13:16 GMT

You are the best out of best, brother!!

Thanks a ton!! You solved my issue!!!

Thanks again 🙂 🙂