Compare the currrent row and previous row

[kumarkp412]

Hi friends,

I have below one scenerio

CID	CType	Panid
1	OLD	AB123
2	OLD	AB124
3	Existing	BC12
4	Existing	CC123
5	Existing	DD12
6	New	BC12
7	New	CC123
8	New	DD1234

and i want to add the new column as flag below scnerio

A) if panid is same for both Eixsting Ctype and New Ctype then the flag have as existing .

B) If Panid is avaliable in Existing Ctype and not avaliable in New Ctype then the flag as Existing.

C) If Panid is avaliable in New Ctype and not avaliable in Existing then the flag as New

D) If panid is available in Old Ctype then in these case should not compare and for this flag as OLD

It looks likes as below.

CID	CType	Panid	Flag
1	OLD	AB123	OLD
2	OLD	AB124	OLD
3	Existing	BC12	Existing
4	Existing	CC123	Existing
5	Existing	DD12	Existing
6	New	BC12	Existing
7	New	CC123	Existing
8	New	DD1234	New

And that to i need to create the flag in the scirpt editor and not in the front end. Because based on this i need to implement another scnerio.

Can any one please help me on this.

Thanks

Kumar KVP

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD *,

Match(CType, 'OLD', 'Existing', 'New') as Order;

LOAD * INLINE [

    CID, CType, Panid

    1, OLD, AB123

    2, OLD, AB124

    3, Existing, BC12

    4, Existing, CC123

    5, Existing, DD12

    6, New, BC12

    7, New, CC123

    8, New, DD1234

];

FinalTable:

LOAD *,

If(CType = 'OLD', 'OLD',

If(CType = 'New',

If(Panid = Previous(Panid), 'Existing', 'New'), 'Existing')) as Flag

Resident Table

Order By Panid, Order;

DROP Table Table;

[kumarkp412]

Hi Sunny,

Thanks for your scripts, It works.

Thanks

Kumar KVP

[sunny_talwar]

Super

[pradosh_thakur]

Hi stalwar1‌

can we check the next row with peek(field,rowno()+1,'table') ?  Didn't work for me.

[sunny_talwar]

Why do you want to peek for more than 1 row back? Not sure I understand RowNo() in Peek?

[pradosh_thakur]

next record i was looking for. so used rowno()+1

[sunny_talwar]

But RowNo() will be changing itself, right?

I mean If this is the data is like this

Dim, Sales, RowNo

AB, 20, 1

AB, 30, 2

CD, 20, 3

So, with rowno() + 1, you will be picking 2 rows first time, 3 for the second row and 4 for the third row and so on and so forth....

[sunny_talwar]

Oh I see what you mean.... you want to peek forward rather than backward? You cannot keep forward, but you can reverse sort your data so that the forward data is backward and then peek backward....

[pradosh_thakur]

my bad .. got it .. thanks ..