Survey banner
Switching to Dataiku - a new area to help users who are transitioning from other tools and diving into Dataiku! CHECK IT OUT

Generate Tile Num and Tile Sequence

Solved!
satishkurra
Level 2
Generate Tile Num and Tile Sequence

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

0 Kudos
1 Solution

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

View solution in original post

0 Kudos
6 Replies

Hi @satishkurra ,

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

0 Kudos
satishkurra
Level 2
Author

Hi, Apologies. Attached.

0 Kudos

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

0 Kudos
satishkurra
Level 2
Author

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

0 Kudos

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

0 Kudos
satishkurra
Level 2
Author

Worked perfectly. Thank you so much.

Labels

?

Setup info

?
A banner prompting to get Dataiku