Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
manojin123
Contributor III
Contributor III

Need incremental flag based on Rank value.

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.

 

manojin123_0-1627503171907.png

 

2 Solutions

Accepted Solutions
MayilVahanan

Hi 

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

Thanks & Regards,
Mayil Vahanan R

View solution in original post

Kushal_Chawda

@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;

View solution in original post

15 Replies
Kushal_Chawda

@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;

Kushal_Chawda_0-1627511156767.png

 

manojin123
Contributor III
Contributor III
Author

Is anyone have any suggestions or  idea on above issue?

Any help would be greatly appreciated.

Manoj

anthonyj
Creator
Creator

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;

anthonyj_0-1627539972815.png

 

manojin123
Contributor III
Contributor III
Author

 

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.


idDateTimeRankLastCallExpected Result
Abcdef2021-01-04 13:54:3200Issue 1
Abcdef2021-01-07 14:11:2512021-01-04 13:54:32Issue 1
Abcdef2021-01-11 16:18:2322021-01-07 14:11:25Issue 1
Abcdef2021-01-21 10:40:1132021-01-11 16:18:23Issue 1
Abcdef2021-01-22 15:46:3842021-01-21 10:40:11Issue 1
Abcdef2021-01-25 14:17:2052021-01-22 15:46:38Issue 1
Abcdef2021-01-26 12:26:0962021-01-25 14:17:20Issue 1
Abcdef2021-02-01 13:57:1272021-01-26 12:26:09Issue 1
Abcdef2021-02-05 13:06:5682021-02-01 13:57:12Issue 1
Abcdef2021-02-09 14:03:0992021-02-05 13:06:56Issue 1
Abcdef2021-02-22 15:49:25102021-02-09 14:03:09Issue 1
Abcdef2021-03-04 09:51:54112021-02-22 15:49:25Issue 1
Abcdef2021-03-09 11:06:49122021-03-04 09:51:54Issue 1
Abcdef2021-03-25 10:00:0302021-03-09 11:06:49Issue 2
Abcdef2021-03-29 08:16:5712021-03-25 10:00:03Issue 2
Abcdef2021-03-29 13:45:4122021-03-29 08:16:57Issue 2
Abcdef2021-03-31 14:22:0932021-03-29 13:45:41Issue 2
Abcdef2021-04-02 13:21:0442021-03-31 14:22:09Issue 2
Abcdef2021-04-06 09:52:5152021-04-02 13:21:04Issue 2
efghui2021-04-26 15:32:0102021-04-06 09:52:51Issue 1
efghui2021-04-28 09:07:5912021-04-26 15:32:01Issue 1
efghui2021-04-30 09:19:5422021-04-28 09:07:59Issue 1
efghui2021-05-05 14:34:0332021-04-30 09:19:54Issue 1
efghui2021-05-25 13:38:1502021-05-05 14:34:03Issue 2
efghui2021-06-02 10:50:0912021-05-25 13:38:15Issue 2
efghui2021-06-02 10:53:1022021-06-02 10:50:09Issue 2
efghui2021-06-24 10:17:5502021-06-02 10:53:10Issue 3
efghui2021-06-24 10:21:3512021-06-24 10:17:55Issue 3
efghui2021-06-25 15:44:2422021-06-24 10:21:35Issue 3
efghui2021-06-25 15:46:4532021-06-25 15:44:24Issue 3
efghui2021-07-01 09:35:3342021-06-25 15:46:45Issue 3
efghui2021-07-01 09:38:3052021-07-01 09:35:33Issue 3
efghui2021-07-01 09:53:2562021-07-01 09:38:30Issue 3
efghui2021-07-06 08:22:1672021-07-01 09:53:25Issue 3
efghui2021-07-06 08:25:5782021-07-06 08:22:16Issue 3
efghui2021-07-09 12:16:0892021-07-06 08:25:57Issue 3
efghui2021-07-20 14:24:47102021-07-09 12:16:08Issue 3

 

anthonyj
Creator
Creator

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

MayilVahanan

Hi 

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

Thanks & Regards,
Mayil Vahanan R

View solution in original post

anthonyj
Creator
Creator

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?

manojin123
Contributor III
Contributor III
Author

it worked thank you so much ... 

MayilVahanan

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.

 

Thanks & Regards,
Mayil Vahanan R