topic Re: Script based Flagging using previous and order by . in App Development

Script based Flagging using previous and order by .

prahlad_infy — Fri, 11 Feb 2022 08:25:39 GMT

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

Re: Script based Flagging using previous and order by .

prahlad_infy — Fri, 11 Feb 2022 09:02:29 GMT

@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

Re: Script based Flagging using previous and order by .

chris_djih — Fri, 11 Feb 2022 11:13:02 GMT

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;

Re: Script based Flagging using previous and order by .

prahlad_infy — Sat, 12 Feb 2022 05:38:18 GMT

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 ?