Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
manojin123
Contributor III
Contributor III
‎2021-07-28 04:19 PM

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

 

Preview file
12 KB
1,937 Views
0 Likes
2 Solutions

Accepted Solutions
MayilVahanan
MVP
MVP
‎2021-07-29 02:35 AM

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
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

1,266 Views
Kushal_Chawda
MVP
MVP
‎2021-07-30 06:32 AM

@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

1,549 Views
0 Likes
15 Replies
Kushal_Chawda
MVP
MVP
‎2021-07-28 06:26 PM

@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

 

1,118 Views
manojin123
Contributor III
Contributor III
‎2021-07-28 11:57 PM
Author

Is anyone have any suggestions or  idea on above issue?

Any help would be greatly appreciated.

Manoj

1,342 Views
0 Likes
anthonyj
Creator III
Creator III
‎2021-07-29 02:16 AM

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

 

1,315 Views
manojin123
Contributor III
Contributor III
‎2021-07-29 02:27 AM
Author

In response to anthonyj

 

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

 

1,301 Views
0 Likes
anthonyj
Creator III
Creator III
‎2021-07-29 02:30 AM

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

1,277 Views
0 Likes
MayilVahanan
MVP
MVP
‎2021-07-29 02:35 AM

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
Please close the thread by marking correct answer & give likes if you like the post.
1,267 Views
anthonyj
Creator III
Creator III
‎2021-07-29 02:51 AM

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?

1,249 Views
0 Likes
manojin123
Contributor III
Contributor III
‎2021-07-29 02:53 AM
Author

In response to MayilVahanan

it worked thank you so much ... 

1,240 Views
0 Likes
MayilVahanan
MVP
MVP
‎2021-07-29 03:00 AM

In response to anthonyj

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
Please close the thread by marking correct answer & give likes if you like the post.
1,234 Views