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: 
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
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,954 Views
0 Likes
15 Replies
manojin123
Contributor III
Contributor III
‎2021-07-29 06:19 AM
Author

In response to MayilVahanan

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.

 

manojin123_0-1627553949818.png

 

 

 

 

idDateTimeRankLastCallExpected ResultLast AgentAGENT NAME
Abcdef2021-01-04 13:54:3200Issue 1Bailee PromAleida Sidney
Abcdef2021-01-07 14:11:2512021-01-04 13:54:32Issue 1Bailee PromMichele Smart
Abcdef2021-01-11 16:18:2322021-01-07 14:11:25Issue 1Bailee PromAmy Nall
Abcdef2021-01-21 10:40:1132021-01-11 16:18:23Issue 1Bailee PromJane Lamfers
Abcdef2021-01-22 15:46:3842021-01-21 10:40:11Issue 1Bailee PromAlan **bleep**an
Abcdef2021-01-25 14:17:2052021-01-22 15:46:38Issue 1Bailee PromMichael Gaydos
Abcdef2021-01-26 12:26:0962021-01-25 14:17:20Issue 1Bailee PromMichael Gaydos
Abcdef2021-02-01 13:57:1272021-01-26 12:26:09Issue 1Bailee PromAlan **bleep**an
Abcdef2021-02-05 13:06:5682021-02-01 13:57:12Issue 1Bailee PromCleanna Castro Fessler
Abcdef2021-02-09 14:03:0992021-02-05 13:06:56Issue 1Bailee PromDavid Williams
Abcdef2021-02-22 15:49:25102021-02-09 14:03:09Issue 1Bailee PromLiz Etouke
Abcdef2021-03-04 09:51:54112021-02-22 15:49:25Issue 1Bailee PromEmanuel Uzoka
Abcdef2021-03-09 11:06:49122021-03-04 09:51:54Issue 1Bailee PromBailee Prom
Abcdef2021-03-25 10:00:0302021-03-09 11:06:49Issue 2Jolene BarkemaKevin Burton
Abcdef2021-03-29 08:16:5712021-03-25 10:00:03Issue 2Jolene BarkemaCecilia Conrad
Abcdef2021-03-29 13:45:4122021-03-29 08:16:57Issue 2Jolene BarkemaMichael Gaydos
Abcdef2021-03-31 14:22:0932021-03-29 13:45:41Issue 2Jolene BarkemaYaritza Quezada
Abcdef2021-04-02 13:21:0442021-03-31 14:22:09Issue 2Jolene BarkemaYaritza Quezada
Abcdef2021-04-06 09:52:5152021-04-02 13:21:04Issue 2Jolene BarkemaJolene Barkema
efghui2021-04-26 15:32:0102021-04-06 09:52:51Issue 1Nickie BurressEmanuel Uzoka
efghui2021-04-28 09:07:5912021-04-26 15:32:01Issue 1Nickie BurressYaritza Quezada
efghui2021-04-30 09:19:5422021-04-28 09:07:59Issue 1Nickie BurressYaritza Quezada
efghui2021-05-05 14:34:0332021-04-30 09:19:54Issue 1Nickie BurressNickie Burress
efghui2021-05-25 13:38:1502021-05-05 14:34:03Issue 2Yaritza QuezadaAmy Nall
efghui2021-06-02 10:50:0912021-05-25 13:38:15Issue 2Yaritza QuezadaTara Carson
efghui2021-06-02 10:53:1022021-06-02 10:50:09Issue 2Yaritza QuezadaYaritza Quezada

 

623 Views
manojin123
Contributor III
Contributor III
‎2021-07-29 01:54 PM
Author

In response to Kushal_Chawda

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.

manojin123_0-1627581254424.png

 

 

idDateTimeRankLastCallExpected ResultLast AgentAGENT NAME
Abcdef2021-01-04 13:54:3200Issue 1Bailee PromAleida Sidney
Abcdef2021-01-07 14:11:2512021-01-04 13:54:32Issue 1Bailee PromMichele Smart
Abcdef2021-01-11 16:18:2322021-01-07 14:11:25Issue 1Bailee PromAmy Nall
Abcdef2021-01-21 10:40:1132021-01-11 16:18:23Issue 1Bailee PromJane Lamfers
Abcdef2021-01-22 15:46:3842021-01-21 10:40:11Issue 1Bailee PromAlan **bleep**an
Abcdef2021-01-25 14:17:2052021-01-22 15:46:38Issue 1Bailee PromMichael Gaydos
Abcdef2021-01-26 12:26:0962021-01-25 14:17:20Issue 1Bailee PromMichael Gaydos
Abcdef2021-02-01 13:57:1272021-01-26 12:26:09Issue 1Bailee PromAlan **bleep**an
Abcdef2021-02-05 13:06:5682021-02-01 13:57:12Issue 1Bailee PromCleanna Castro Fessler
Abcdef2021-02-09 14:03:0992021-02-05 13:06:56Issue 1Bailee PromDavid Williams
Abcdef2021-02-22 15:49:25102021-02-09 14:03:09Issue 1Bailee PromLiz Etouke
Abcdef2021-03-04 09:51:54112021-02-22 15:49:25Issue 1Bailee PromEmanuel Uzoka
Abcdef2021-03-09 11:06:49122021-03-04 09:51:54Issue 1Bailee PromBailee Prom
Abcdef2021-03-25 10:00:0302021-03-09 11:06:49Issue 2Jolene BarkemaKevin Burton
Abcdef2021-03-29 08:16:5712021-03-25 10:00:03Issue 2Jolene BarkemaCecilia Conrad
Abcdef2021-03-29 13:45:4122021-03-29 08:16:57Issue 2Jolene BarkemaMichael Gaydos
Abcdef2021-03-31 14:22:0932021-03-29 13:45:41Issue 2Jolene BarkemaYaritza Quezada
Abcdef2021-04-02 13:21:0442021-03-31 14:22:09Issue 2Jolene BarkemaYaritza Quezada
Abcdef2021-04-06 09:52:5152021-04-02 13:21:04Issue 2Jolene BarkemaJolene Barkema
efghui2021-04-26 15:32:0102021-04-06 09:52:51Issue 1Nickie BurressEmanuel Uzoka
efghui2021-04-28 09:07:5912021-04-26 15:32:01Issue 1Nickie BurressYaritza Quezada
efghui2021-04-30 09:19:5422021-04-28 09:07:59Issue 1Nickie BurressYaritza Quezada
efghui2021-05-05 14:34:0332021-04-30 09:19:54Issue 1Nickie BurressNickie Burress
efghui2021-05-25 13:38:1502021-05-05 14:34:03Issue 2Yaritza QuezadaAmy Nall
efghui2021-06-02 10:50:0912021-05-25 13:38:15Issue 2Yaritza QuezadaTara Carson
efghui2021-06-02 10:53:1022021-06-02 10:50:09Issue 2Yaritza QuezadaYaritza Quezada
544 Views
0 Likes
Kushal_Chawda
MVP
MVP
‎2021-07-29 05:35 PM

@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;
530 Views
0 Likes
manojin123
Contributor III
Contributor III
‎2021-07-30 02:46 AM
Author

In response to Kushal_Chawda

Hi Kushal,

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

 

Thank 
Manoj

509 Views
Kushal_Chawda
MVP
MVP
‎2021-07-30 04:24 AM

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

 

490 Views
0 Likes
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;
1,566 Views
0 Likes