Generate Tile Num and Tile Sequence

satishkurra

Hi team

I'm trying to populate the Tile Num and Tile Sequence Number in the attached picture format. Trying to use windows recipe with no luck.

Can someone please help with this?

Attached is the data, the ask is to make sure generate a tile num for INS column. Highlighted the color combinations in the picture.

---

Operating system used: Browser

LouisDHulst

I have a solution with 2 window recipes and 1 prepare recipe, but it relies on the field RecordID being available and unique.

1st Window Recipe:

Define two windows:

1. partition on INS and orderby RecordID (ascending order)
2. order by RecordID (ascending order)

For aggregations, compute Sum of INS.

Prepare Recipe:

Use a formula processor with the formula

if(INS == 1, INS_sum * 2, if(w2_INS_sum == 0, 1, w2_INS_sum * 2 + 1))

This gives you Tile_Num

2nd Window Recipe:

Only a single window: partition by Tile_Num and order by RecordID (ascending), then compute the Rank. This gives you Tile_SequenceNum

LouisDHulst

Hi @satishkurra
,

I'm not seeing any attachments, could you add them so we can take a look? Thanks

satishkurra

Hi, Apologies. Attached.

LouisDHulst

Are you able to use SQL recipes/ custom aggregations using SQL? And how large would be full dataset be?

satishkurra

My dataset is in CSV and has around 1.5 MILLION. In my oRG, SQL recipe is showing disabled.

satishkurra

Worked perfectly. Thank you so much.