Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
prahlad_infy
Partner - Creator II
Partner - Creator II

Script based Flagging using previous and order by .

Hi All ,

I have set of data which i have already ordered by and tried to create flag using previous . But not able to achieve 

correct flagging . Please suggest a script .

Even though table have multiple columns but only Below  fields are involved in reaching flag logic . Other fields are not involved .

Group SubGroup Country YearTime Sub Rating

 

LOAD * INLINE [

Group, SubGroup, Country, YearTime, Sub, Rating,Col1,col2
A, A1, C1, 2019, Q4, 4
A, A1, C1, 2020, Q2, 3
A, A1, C2, 2020, Q1, 7
A, A1, C2, 2020, Q3, 9
A, B1, C1, 2018, Q3, 1
A, B1, C1, 2020, Q3, 6
B, B1, C1, 2020, Q2, 3
B, B1, C1, 2020, Q3, 5
B, B1, C1, 2020, Q4, 3
C, B1, C1, 2019, Q1, 2
D, B1, C1, 2018, Q4, 5
D, B1, C1, 2019, Q3, 5
];

 

 

flag.PNG

 

Data :

Group SubGroup Country YearTime Sub Rating Flag logic Columnx Columny Columnz
A A1 C1 2019 Q4 4 if there is no previous record flag = -1      
A A1 C1 2020 Q2 3 Rank value came down from 4 to 3 , then Flag =1      
A A1 C2 2020 Q1 7 if there is no previous record flag = -1      
A A1 C2 2020 Q3 9 Rank value increased  from 7 to 9 , then Flag =2      
A B1 C1 2018 Q3 1 if there is no previous record flag = -1      
A B1 C1 2020 Q3 6 Rank value increased  from 1 to 6 , then Flag =2      
B B1 C1 2020 Q2 3 if there is no previous record flag = -1      
B B1 C1 2020 Q3 5 Rank value increased  from 3 to 5 , then Flag =2      
B B1 C1 2020 Q4 3 Rank value came down from 5 to 3 , then Flag =1      
C B1 C1 2019 Q1 2 if there is no previous record flag = -1      
D B1 C1 2018 Q4 5 if there is no previous record flag = -1      
D B1 C1 2019 Q3 5 Rank value remains un changed from 5 , flag = 0      
Labels (1)
1 Solution

Accepted Solutions
chris_djih
Creator III
Creator III

First i would add a unique Key-Field to identify which rows belongs together, like @prahlad_infy  mentioned.
something like: Group &'-'& SubGroup &'-'& Country as ID (or  use Autonumber for maximal performance)

Then you have to always check: Do the previous ID belongs to the same ID as the actual row? and then you can make your flag conditions:

 

Finaltable:
Load *,
  If(previous(ID) = ID,
    If(Previous(Rating)>Rating,1,
    If(Previous(Rating)<Rating,2,0)),-1)  as Flag
resident data;

 

 

 

 

If you found help, mark the correct answer and give some likes to ALL contributors, that tried to help.

View solution in original post

3 Replies
prahlad_infy
Partner - Creator II
Partner - Creator II
Author

@MarcoWedel 

Normal previous flag logic which is used takes previous rating even though its out of group .

If(Previous(Rating)>Rating,1,If(Previous(Rating)=Rating,0,2)) AS Rating_flag

Basically it should be such that only those previous records should be fetched which falls within same category.

When i say same category , i mean 

 

Each backrground color represents a category 

group.PNG

chris_djih
Creator III
Creator III

First i would add a unique Key-Field to identify which rows belongs together, like @prahlad_infy  mentioned.
something like: Group &'-'& SubGroup &'-'& Country as ID (or  use Autonumber for maximal performance)

Then you have to always check: Do the previous ID belongs to the same ID as the actual row? and then you can make your flag conditions:

 

Finaltable:
Load *,
  If(previous(ID) = ID,
    If(Previous(Rating)>Rating,1,
    If(Previous(Rating)<Rating,2,0)),-1)  as Flag
resident data;

 

 

 

 

If you found help, mark the correct answer and give some likes to ALL contributors, that tried to help.
prahlad_infy
Partner - Creator II
Partner - Creator II
Author

Oh yes . Thanks for replying .

Instead of creating autoumber keys , i was trying to do something around group by with previous .

Is there any alternate solution possible . Just asking ?