Indexing based on a row value

[Anonymous]

Hello All, I need some help in creating index column for below scenario.

mud_id	visitor_id	session_id	Time stamp	Page Name
sk4844625	523012	202020	10/22/19 10:29 AM	Hello1
sk4844625	523012	202020	10/22/19 10:30 AM	Projects Page
sk4844625	523012	202020	10/22/19 10:32 AM	Click on A Project - Project1
sk4844625	523012	202020	10/22/19 10:34 AM	Click on "Connect an API (bottom right on Project)
sk4844625	523012	202020	10/22/19 10:36 AM	API catalog page
sk4844625	523012	202020	10/22/19 10:38 AM	Select an API - API details page
sk4844625	523012	202020	10/22/19 10:40 AM	Connect to Project button
sk4844625	523012	202020	10/22/19 10:42 AM	Select API environment
sk4844625	523012	202020	10/22/19 10:44 AM	Hit Connect
sk4844625	523012	202020	10/22/19 10:46 AM	Projects Page
sk4844625	523012	202020	10/22/19 10:48 AM	Click on A Project - Project1
sk4844625	523012	202020	10/22/19 10:50 AM	Click on "Connect an API (bottom right on Project)
sk4844625	523012	202020	10/22/19 10:52 AM	API catalog page
sk4844625	523012	202020	10/22/19 10:54 AM	Select an API - API details page
sk4844625	523012	202020	10/22/19 10:56 AM	Connect to Project button
sk4844625	523012	202020	10/22/19 10:58 AM	Select API environment
sk4844625	523012	202020	10/22/19 10:58 AM	Hello2
sk4844625	523012	202020	10/22/19 11:00 AM	Projects Page
sk4844625	523012	202020	10/22/19 11:02 AM	Click on A Project - Project1
sk4844625	523012	202020	10/22/19 11:04 AM	Click on "Connect an API (bottom right on Project)
sk4844625	523012	202020	10/22/19 11:06 AM	API catalog page
sk4844625	523012	202021	10/22/19 11:08 AM	Select an API - API details page
sk4844625	523012	202021	10/22/19 11:10 AM	Connect to Project button
GK321456	432145	123421	10/22/19 10:30 AM	Hep123
GK321456	432145	123421	10/22/19 10:30 AM	Hep12312fdrg
GK321456	432145	123421	10/22/19 10:31 AM	Projects Page
GK321456	432145	123421	10/22/19 10:33 AM	Click on A Project - Project1
GK321456	432145	123421	10/22/19 10:35 AM	Click on "Connect an API (bottom right on Project)
GK321456	432145	123421	10/22/19 10:37 AM	API catalog page
GK321456	432145	123421	10/22/19 10:39 AM	Select an API - API details page
GK321456	432145	123421	10/22/19 10:41 AM	Connect to Project button
GK321456	432145	123421	10/22/19 10:43 AM	Select API environment
GK321456	432145	123421	10/22/19 10:45 AM	Hit Connect
GK321456	432145	123421	10/22/19 10:47 AM	Projects Page
GK321456	432145	123421	10/22/19 10:49 AM	Click on A Project - Project1
GK321456	432145	123421	10/22/19 10:51 AM	Click on "Connect an API (bottom right on Project)
GK321456	432145	123421	10/22/19 10:53 AM	API catalog page
GK321456	432145	123421	10/22/19 10:55 AM	Select an API - API details page
GK321456	432145	123421	10/22/19 10:57 AM	Connect to Project button
GK321456	432145	123421	10/22/19 10:59 AM	Select API environment

Please see above table,

Need help in adding index column based on "Page Name" Column.

Index Number should start from row Value "Project Info"(This row index should be 1) and end at "Hit Connect"(This row index value should be 8, Filter should be based on Mudid & Time Stamp, as there is chance multi user can use website same time)

-Some times "Hit connect" wont be the last step, instead previous step(s). New solution should be intellegent enough to handle indexing this way.

Really appreciate if anyone can help. Thanks in advance.

Regards,

Siva

[amitchandak]

At first thought, you can create an index like

index = countx(filter(table, table[mud_id]=earlier(mud_id) && table[timestamp]<=earlier(table[timestamp])),table[timestamp])

If you can provide a number to the steps then and index can reset based on that

[Anonymous]

Thanks for the reply. 

I tried suggested solution, but indexing happens based on Mudid,mean starts from 1 for the mudid.

I need  something like below (Index based on Page Name)

mud_id	visitor_id	session_id	Time stamp	Page Name	Index
sk4844625	523012	202020	10/22/19 10:29 AM	Hello1
sk4844625	523012	202020	10/22/19 10:30 AM	Projects Page	1
sk4844625	523012	202020	10/22/19 10:32 AM	Click on A Project - Project1	2
sk4844625	523012	202020	10/22/19 10:34 AM	Click on "Connect an API (bottom right on Project)	3
sk4844625	523012	202020	10/22/19 10:36 AM	API catalog page	4
sk4844625	523012	202020	10/22/19 10:38 AM	Select an API - API details page	5
sk4844625	523012	202020	10/22/19 10:40 AM	Connect to Project button	6
sk4844625	523012	202020	10/22/19 10:42 AM	Select API environment	7
sk4844625	523012	202020	10/22/19 10:44 AM	Hit Connect	8
sk4844625	523012	202020	10/22/19 10:46 AM	Projects Page	1
sk4844625	523012	202020	10/22/19 10:48 AM	Click on A Project - Project1	2
sk4844625	523012	202020	10/22/19 10:50 AM	Click on "Connect an API (bottom right on Project)	3
sk4844625	523012	202020	10/22/19 10:52 AM	API catalog page	4
sk4844625	523012	202020	10/22/19 10:54 AM	Select an API - API details page	5
sk4844625	523012	202020	10/22/19 10:56 AM	Connect to Project button	6
sk4844625	523012	202020	10/22/19 10:58 AM	Select API environment	7
sk4844625	523012	202020	10/22/19 10:58 AM	Hello2
sk4844625	523012	202020	10/22/19 11:00 AM	Projects Page	1
sk4844625	523012	202020	10/22/19 11:02 AM	Click on A Project - Project1	2
sk4844625	523012	202020	10/22/19 11:04 AM	Click on "Connect an API (bottom right on Project)	3
sk4844625	523012	202020	10/22/19 11:06 AM	API catalog page	4
sk4844625	523012	202021	10/22/19 11:08 AM	Select an API - API details page	5
sk4844625	523012	202021	10/22/19 11:10 AM	Connect to Project button	6
GK321456	432145	123421	10/22/19 10:30 AM	Hep123
GK321456	432145	123421	10/22/19 10:30 AM	Hep12312fdrg
GK321456	432145	123421	10/22/19 10:31 AM	Projects Page	1
GK321456	432145	123421	10/22/19 10:33 AM	Click on A Project - Project1	2
GK321456	432145	123421	10/22/19 10:35 AM	Click on "Connect an API (bottom right on Project)	3
GK321456	432145	123421	10/22/19 10:37 AM	API catalog page	4
GK321456	432145	123421	10/22/19 10:39 AM	Select an API - API details page	5
GK321456	432145	123421	10/22/19 10:41 AM	Connect to Project button	6
GK321456	432145	123421	10/22/19 10:43 AM	Select API environment	7
GK321456	432145	123421	10/22/19 10:45 AM	Hit Connect	8

Thanks,

SIva