topic Re: Need incremental flag based on Rank value. in App Development

Need incremental flag based on Rank value.

manojin123 — Wed, 28 Jul 2021 20:19:03 GMT

Hi All,

Hope all is well,

Please help me on one scenario where I need a code which provide me incremental Flag numbers based on id , Date and Rank.

For example

if a id have four zero ranking then it should be show the result like for first Zero the flag will be Issue1 for second zero rank Flag will be Issue2.. and so on.

I am attaching a sample data with expected result column.

Re: Need incremental flag based on Rank value.

Kushal_Chawda — Wed, 28 Jul 2021 22:26:08 GMT

@manojin123 try below

Data: LOAD id, DateTime, Rank, LastCall FROM [C:\sample_Data.xlsx] (ooxml, embedded labels, table is Sheet1); Final: LOAD *, if(id<>Previous(id) or RowNo()=1,'Issue '&1, if(id=Previous(id) and Rank=0, 'Issue '&rangesum(keepchar(Peek('issue'),'0123456789'),1),Peek('issue'))) as issue Resident Data Order by id,DateTime,Rank; DROP Table Data;

Re: Need incremental flag based on Rank value.

manojin123 — Thu, 29 Jul 2021 03:57:23 GMT

Is anyone have any suggestions or idea on above issue?

Any help would be greatly appreciated.

Manoj

Re: Need incremental flag based on Rank value.

anthonyj — Thu, 29 Jul 2021 06:26:28 GMT

Hi Manoj,

What a great question. Left me scratching my head for a while but here's a result I've worked out. Make sure your data is sorted by your date and rank fields ascending.

data:
load * inline [
id DateTime Rank LastCall
Abcdef 2021-01-04 13:54:32 0 0
Abcdef 2021-01-07 14:11:25 1 2021-01-04 13:54:32
Abcdef 2021-01-11 16:18:23 2 2021-01-07 14:11:25
Abcdef 2021-01-21 10:40:11 3 2021-01-11 16:18:23
Abcdef 2021-01-22 15:46:38 4 2021-01-21 10:40:11
Abcdef 2021-01-25 14:17:20 5 2021-01-22 15:46:38
Abcdef 2021-01-26 12:26:09 6 2021-01-25 14:17:20
Abcdef 2021-02-01 13:57:12 7 2021-01-26 12:26:09
Abcdef 2021-02-05 13:06:56 8 2021-02-01 13:57:12
Abcdef 2021-02-09 14:03:09 9 2021-02-05 13:06:56
Abcdef 2021-02-22 15:49:25 10 2021-02-09 14:03:09
Abcdef 2021-03-04 09:51:54 11 2021-02-22 15:49:25
Abcdef 2021-03-09 11:06:49 12 2021-03-04 09:51:54
Abcdef 2021-03-25 10:00:03 0 2021-03-09 11:06:49
Abcdef 2021-03-29 08:16:57 1 2021-03-25 10:00:03
Abcdef 2021-03-29 13:45:41 2 2021-03-29 08:16:57
Abcdef 2021-03-31 14:22:09 3 2021-03-29 13:45:41
Abcdef 2021-04-02 13:21:04 4 2021-03-31 14:22:09
Abcdef 2021-04-06 09:52:51 5 2021-04-02 13:21:04
Abcdef 2021-04-26 15:32:01 0 2021-04-06 09:52:51
Abcdef 2021-04-28 09:07:59 1 2021-04-26 15:32:01
Abcdef 2021-04-30 09:19:54 2 2021-04-28 09:07:59
Abcdef 2021-05-05 14:34:03 3 2021-04-30 09:19:54
Abcdef 2021-05-25 13:38:15 0 2021-05-05 14:34:03
Abcdef 2021-06-02 10:50:09 1 2021-05-25 13:38:15
Abcdef 2021-06-02 10:53:10 2 2021-06-02 10:50:09
Abcdef 2021-06-24 10:17:55 0 2021-06-02 10:53:10
Abcdef 2021-06-24 10:21:35 1 2021-06-24 10:17:55
Abcdef 2021-06-25 15:44:24 2 2021-06-24 10:21:35
Abcdef 2021-06-25 15:46:45 3 2021-06-25 15:44:24
Abcdef 2021-07-01 09:35:33 4 2021-06-25 15:46:45
Abcdef 2021-07-01 09:38:30 5 2021-07-01 09:35:33
Abcdef 2021-07-01 09:53:25 6 2021-07-01 09:38:30
Abcdef 2021-07-06 08:22:16 7 2021-07-01 09:53:25
Abcdef 2021-07-06 08:25:57 8 2021-07-06 08:22:16
Abcdef 2021-07-09 12:16:08 9 2021-07-06 08:25:57
Abcdef 2021-07-20 14:24:47 10 2021-07-09 12:16:08
] (delimiter is '\t');

final:
load *,
'Issue ' & counter as IssueNumber;

load *,
if(Rank = 0,
1 + coalesce(peek('counter'),0),
peek('counter')) as counter

resident data
order by DateTime, Rank;

drop table data;

Re: Need incremental flag based on Rank value.

manojin123 — Thu, 29 Jul 2021 06:27:21 GMT

Yes its working for single ID. but not for multiple ID. if I have multiple id and have same scenario then its not working like that.

id	DateTime	Rank	LastCall	Expected Result
Abcdef	2021-01-04 13:54:32	0	0	Issue 1
Abcdef	2021-01-07 14:11:25	1	2021-01-04 13:54:32	Issue 1
Abcdef	2021-01-11 16:18:23	2	2021-01-07 14:11:25	Issue 1
Abcdef	2021-01-21 10:40:11	3	2021-01-11 16:18:23	Issue 1
Abcdef	2021-01-22 15:46:38	4	2021-01-21 10:40:11	Issue 1
Abcdef	2021-01-25 14:17:20	5	2021-01-22 15:46:38	Issue 1
Abcdef	2021-01-26 12:26:09	6	2021-01-25 14:17:20	Issue 1
Abcdef	2021-02-01 13:57:12	7	2021-01-26 12:26:09	Issue 1
Abcdef	2021-02-05 13:06:56	8	2021-02-01 13:57:12	Issue 1
Abcdef	2021-02-09 14:03:09	9	2021-02-05 13:06:56	Issue 1
Abcdef	2021-02-22 15:49:25	10	2021-02-09 14:03:09	Issue 1
Abcdef	2021-03-04 09:51:54	11	2021-02-22 15:49:25	Issue 1
Abcdef	2021-03-09 11:06:49	12	2021-03-04 09:51:54	Issue 1
Abcdef	2021-03-25 10:00:03	0	2021-03-09 11:06:49	Issue 2
Abcdef	2021-03-29 08:16:57	1	2021-03-25 10:00:03	Issue 2
Abcdef	2021-03-29 13:45:41	2	2021-03-29 08:16:57	Issue 2
Abcdef	2021-03-31 14:22:09	3	2021-03-29 13:45:41	Issue 2
Abcdef	2021-04-02 13:21:04	4	2021-03-31 14:22:09	Issue 2
Abcdef	2021-04-06 09:52:51	5	2021-04-02 13:21:04	Issue 2
efghui	2021-04-26 15:32:01	0	2021-04-06 09:52:51	Issue 1
efghui	2021-04-28 09:07:59	1	2021-04-26 15:32:01	Issue 1
efghui	2021-04-30 09:19:54	2	2021-04-28 09:07:59	Issue 1
efghui	2021-05-05 14:34:03	3	2021-04-30 09:19:54	Issue 1
efghui	2021-05-25 13:38:15	0	2021-05-05 14:34:03	Issue 2
efghui	2021-06-02 10:50:09	1	2021-05-25 13:38:15	Issue 2
efghui	2021-06-02 10:53:10	2	2021-06-02 10:50:09	Issue 2
efghui	2021-06-24 10:17:55	0	2021-06-02 10:53:10	Issue 3
efghui	2021-06-24 10:21:35	1	2021-06-24 10:17:55	Issue 3
efghui	2021-06-25 15:44:24	2	2021-06-24 10:21:35	Issue 3
efghui	2021-06-25 15:46:45	3	2021-06-25 15:44:24	Issue 3
efghui	2021-07-01 09:35:33	4	2021-06-25 15:46:45	Issue 3
efghui	2021-07-01 09:38:30	5	2021-07-01 09:35:33	Issue 3
efghui	2021-07-01 09:53:25	6	2021-07-01 09:38:30	Issue 3
efghui	2021-07-06 08:22:16	7	2021-07-01 09:53:25	Issue 3
efghui	2021-07-06 08:25:57	8	2021-07-06 08:22:16	Issue 3
efghui	2021-07-09 12:16:08	9	2021-07-06 08:25:57	Issue 3
efghui	2021-07-20 14:24:47	10	2021-07-09 12:16:08	Issue 3

Re: Need incremental flag based on Rank value.

anthonyj — Thu, 29 Jul 2021 06:30:16 GMT

Sorry, the data didn't have different ID's. I'll give it another go. Thanks.

Re: Need incremental flag based on Rank value.

MayilVahanan — Thu, 29 Jul 2021 06:35:52 GMT

Try like below

LOAD id,
DateTime,
Rank,
LastCall,
If((id = Peek(id) or RowNo()=1) and Rank = 0, Alt(Peek('ER'),0)+1, if(id <> Peek(id), 1, Peek('ER'))) as ER,
[Expected Result]
FROM
[sample_Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Hope Id, Rank, DateTime are in asc

Re: Need incremental flag based on Rank value.

anthonyj — Thu, 29 Jul 2021 06:51:22 GMT

That's a great result. Very elegant. The "or RowNo() = 1" doesn't seem to be required as the Alt() function is taking care of the null created in the first instance. Is there another reason for this or is it just good practice?

Re: Need incremental flag based on Rank value.

manojin123 — Thu, 29 Jul 2021 06:53:40 GMT

it worked thank you so much ...

Re: Need incremental flag based on Rank value.

MayilVahanan — Thu, 29 Jul 2021 07:00:25 GMT

Hi Anthony,

For sample (single ID), I have tried with below expression, for first line, condition will failed, so it goes to else condition, and result will be blank. So added Rowno() =1 like second expression

1. If(id = Peek(id) and Rank = 0, Alt(Peek('ER'),0)+1, if(id <> Peek(id), 1, Peek('ER'))) as ER

2. If((id = Peek(id) or RowNo()=1) and Rank = 0, Alt(Peek('ER'),0)+1, Peek('ER')) as ER

For multiple Id, Rowno()=1 not required, because highlighted if condition take cares of it.

If((id = Peek(id) or RowNo()=1) and Rank = 0, Alt(Peek('ER'),0)+1, if(id <> Peek(id), 1, Peek('ER'))) as ER

simplified exp: give expected result.

If((id = Peek(id)) and Rank = 0, Alt(Peek('ER'),0)+1, if(id <> Peek(id), 1, Peek('ER'))) as ER

Thanks for asking it.

Re: Need incremental flag based on Rank value.

manojin123 — Thu, 29 Jul 2021 10:19:33 GMT

Hi Vahanan

One more question if i need to find Last Agent name based on id, Rank and Datetime on same dataset. How can i do that. Do i need to use the same logic and sort that data in desc order. Please suggest.

id	DateTime	Rank	LastCall	Expected Result	Last Agent	AGENT NAME
Abcdef	2021-01-04 13:54:32	0	0	Issue 1	Bailee Prom	Aleida Sidney
Abcdef	2021-01-07 14:11:25	1	2021-01-04 13:54:32	Issue 1	Bailee Prom	Michele Smart
Abcdef	2021-01-11 16:18:23	2	2021-01-07 14:11:25	Issue 1	Bailee Prom	Amy Nall
Abcdef	2021-01-21 10:40:11	3	2021-01-11 16:18:23	Issue 1	Bailee Prom	Jane Lamfers
Abcdef	2021-01-22 15:46:38	4	2021-01-21 10:40:11	Issue 1	Bailee Prom	Alan bleepan
Abcdef	2021-01-25 14:17:20	5	2021-01-22 15:46:38	Issue 1	Bailee Prom	Michael Gaydos
Abcdef	2021-01-26 12:26:09	6	2021-01-25 14:17:20	Issue 1	Bailee Prom	Michael Gaydos
Abcdef	2021-02-01 13:57:12	7	2021-01-26 12:26:09	Issue 1	Bailee Prom	Alan bleepan
Abcdef	2021-02-05 13:06:56	8	2021-02-01 13:57:12	Issue 1	Bailee Prom	Cleanna Castro Fessler
Abcdef	2021-02-09 14:03:09	9	2021-02-05 13:06:56	Issue 1	Bailee Prom	David Williams
Abcdef	2021-02-22 15:49:25	10	2021-02-09 14:03:09	Issue 1	Bailee Prom	Liz Etouke
Abcdef	2021-03-04 09:51:54	11	2021-02-22 15:49:25	Issue 1	Bailee Prom	Emanuel Uzoka
Abcdef	2021-03-09 11:06:49	12	2021-03-04 09:51:54	Issue 1	Bailee Prom	Bailee Prom
Abcdef	2021-03-25 10:00:03	0	2021-03-09 11:06:49	Issue 2	Jolene Barkema	Kevin Burton
Abcdef	2021-03-29 08:16:57	1	2021-03-25 10:00:03	Issue 2	Jolene Barkema	Cecilia Conrad
Abcdef	2021-03-29 13:45:41	2	2021-03-29 08:16:57	Issue 2	Jolene Barkema	Michael Gaydos
Abcdef	2021-03-31 14:22:09	3	2021-03-29 13:45:41	Issue 2	Jolene Barkema	Yaritza Quezada
Abcdef	2021-04-02 13:21:04	4	2021-03-31 14:22:09	Issue 2	Jolene Barkema	Yaritza Quezada
Abcdef	2021-04-06 09:52:51	5	2021-04-02 13:21:04	Issue 2	Jolene Barkema	Jolene Barkema
efghui	2021-04-26 15:32:01	0	2021-04-06 09:52:51	Issue 1	Nickie Burress	Emanuel Uzoka
efghui	2021-04-28 09:07:59	1	2021-04-26 15:32:01	Issue 1	Nickie Burress	Yaritza Quezada
efghui	2021-04-30 09:19:54	2	2021-04-28 09:07:59	Issue 1	Nickie Burress	Yaritza Quezada
efghui	2021-05-05 14:34:03	3	2021-04-30 09:19:54	Issue 1	Nickie Burress	Nickie Burress
efghui	2021-05-25 13:38:15	0	2021-05-05 14:34:03	Issue 2	Yaritza Quezada	Amy Nall
efghui	2021-06-02 10:50:09	1	2021-05-25 13:38:15	Issue 2	Yaritza Quezada	Tara Carson
efghui	2021-06-02 10:53:10	2	2021-06-02 10:50:09	Issue 2	Yaritza Quezada	Yaritza Quezada

Re: Need incremental flag based on Rank value.

manojin123 — Thu, 29 Jul 2021 17:54:49 GMT

Thank you Kushal,

just one more question can i use same logical to get Last Agent name based on Issue flag.. Please see the expected result in image i am also attaching sample data for that.

id	DateTime	Rank	LastCall	Expected Result	Last Agent	AGENT NAME
Abcdef	2021-01-04 13:54:32	0	0	Issue 1	Bailee Prom	Aleida Sidney
Abcdef	2021-01-07 14:11:25	1	2021-01-04 13:54:32	Issue 1	Bailee Prom	Michele Smart
Abcdef	2021-01-11 16:18:23	2	2021-01-07 14:11:25	Issue 1	Bailee Prom	Amy Nall
Abcdef	2021-01-21 10:40:11	3	2021-01-11 16:18:23	Issue 1	Bailee Prom	Jane Lamfers
Abcdef	2021-01-22 15:46:38	4	2021-01-21 10:40:11	Issue 1	Bailee Prom	Alan bleepan
Abcdef	2021-01-25 14:17:20	5	2021-01-22 15:46:38	Issue 1	Bailee Prom	Michael Gaydos
Abcdef	2021-01-26 12:26:09	6	2021-01-25 14:17:20	Issue 1	Bailee Prom	Michael Gaydos
Abcdef	2021-02-01 13:57:12	7	2021-01-26 12:26:09	Issue 1	Bailee Prom	Alan bleepan
Abcdef	2021-02-05 13:06:56	8	2021-02-01 13:57:12	Issue 1	Bailee Prom	Cleanna Castro Fessler
Abcdef	2021-02-09 14:03:09	9	2021-02-05 13:06:56	Issue 1	Bailee Prom	David Williams
Abcdef	2021-02-22 15:49:25	10	2021-02-09 14:03:09	Issue 1	Bailee Prom	Liz Etouke
Abcdef	2021-03-04 09:51:54	11	2021-02-22 15:49:25	Issue 1	Bailee Prom	Emanuel Uzoka
Abcdef	2021-03-09 11:06:49	12	2021-03-04 09:51:54	Issue 1	Bailee Prom	Bailee Prom
Abcdef	2021-03-25 10:00:03	0	2021-03-09 11:06:49	Issue 2	Jolene Barkema	Kevin Burton
Abcdef	2021-03-29 08:16:57	1	2021-03-25 10:00:03	Issue 2	Jolene Barkema	Cecilia Conrad
Abcdef	2021-03-29 13:45:41	2	2021-03-29 08:16:57	Issue 2	Jolene Barkema	Michael Gaydos
Abcdef	2021-03-31 14:22:09	3	2021-03-29 13:45:41	Issue 2	Jolene Barkema	Yaritza Quezada
Abcdef	2021-04-02 13:21:04	4	2021-03-31 14:22:09	Issue 2	Jolene Barkema	Yaritza Quezada
Abcdef	2021-04-06 09:52:51	5	2021-04-02 13:21:04	Issue 2	Jolene Barkema	Jolene Barkema
efghui	2021-04-26 15:32:01	0	2021-04-06 09:52:51	Issue 1	Nickie Burress	Emanuel Uzoka
efghui	2021-04-28 09:07:59	1	2021-04-26 15:32:01	Issue 1	Nickie Burress	Yaritza Quezada
efghui	2021-04-30 09:19:54	2	2021-04-28 09:07:59	Issue 1	Nickie Burress	Yaritza Quezada
efghui	2021-05-05 14:34:03	3	2021-04-30 09:19:54	Issue 1	Nickie Burress	Nickie Burress
efghui	2021-05-25 13:38:15	0	2021-05-05 14:34:03	Issue 2	Yaritza Quezada	Amy Nall
efghui	2021-06-02 10:50:09	1	2021-05-25 13:38:15	Issue 2	Yaritza Quezada	Tara Carson
efghui	2021-06-02 10:53:10	2	2021-06-02 10:50:09	Issue 2	Yaritza Quezada	Yaritza Quezada

Re: Need incremental flag based on Rank value.

Kushal_Chawda — Thu, 29 Jul 2021 21:35:15 GMT

@manojin123 try below

Data: LOAD id, DateTime, Rank, LastCall, Agent_Name FROM [C:\sample_Data.xlsx] (ooxml, embedded labels, table is Sheet1); Left Join(Data) LOAD id, max(Rank) as Max_Rank Resident Data Group by id; Left Join(Data) LOAD id, Agent_Name Resident Data where Rank=Max_Rank; Final: LOAD *, if(id<>Previous(id) or RowNo()=1,'Issue '&1, if(id=Previous(id) and Rank=0, 'Issue '&rangesum(keepchar(Peek('issue'),'0123456789'),1),Peek('issue'))) as issue Resident Data Order by id,DateTime,Rank; DROP Table Data; DROP Field Max_Rank;

Re: Need incremental flag based on Rank value.

manojin123 — Fri, 30 Jul 2021 06:46:48 GMT

Hi Kushal,

I need Last Agent Name who took the call for Issue 1 and same way for Issue2.

Thank
Manoj

Re: Need incremental flag based on Rank value.

Kushal_Chawda — Fri, 30 Jul 2021 08:24:08 GMT

@manojin123 Try below

Data:
LOAD id, 
     DateTime, 
     Rank, 
     LastCall,
     Agent_Name
FROM
[C:\sample_Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join(Data)
LOAD id,
     Rank,
     max(Rank) as Max_Rank   
Resident Data
Group by id,Rank;

Left Join(Data)
LOAD id,
     Agent_Name
Resident Data
where Rank=Max_Rank;

Final:
LOAD *,  
     if(id<>Previous(id) or RowNo()=1,'Issue '&1,
     if(id=Previous(id) and Rank=0, 'Issue '&rangesum(keepchar(Peek('issue'),'0123456789'),1),Peek('issue'))) as issue
Resident Data
Order by id,DateTime,Rank;

DROP Table Data;

DROP Field Max_Rank;

Re: Need incremental flag based on Rank value.

Kushal_Chawda — Fri, 30 Jul 2021 10:32:18 GMT

@manojin123 try below

Data: LOAD id, DateTime, Rank, LastCall, Agent_Name FROM [C:\sample_Data.xlsx] (ooxml, embedded labels, table is Sheet1); Final: LOAD *, if(id<>Previous(id) or RowNo()=1,'Issue '&1, if(id=Previous(id) and Rank=0, 'Issue '&rangesum(keepchar(Peek('issue'),'0123456789'),1),Peek('issue'))) as issue Resident Data Order by id,DateTime,Rank; DROP Table Data; Left Join(Data) LOAD id, issue, max(Rank) as Max_Rank Resident Data Group by id,issue; Left Join(Data) LOAD id, issue, Agent_Name as Last_Agent Resident Data where Rank=Max_Rank; DROP Field Max_Rank;