Script based Flagging using previous and order by .

[prahlad_infy]

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

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

[prahlad_infy]

@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 

[chris_djih · Accepted solution]

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;

[prahlad_infy]

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 ?