Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kumarkp412
Creator II
Creator II

Compare the currrent row and previous row

Hi friends,

I have below one scenerio

CIDCTypePanid
1OLDAB123
2OLDAB124
3ExistingBC12
4ExistingCC123
5ExistingDD12
6NewBC12
7NewCC123
8NewDD1234

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.

CIDCTypePanidFlag
1OLDAB123OLD
2OLDAB124OLD
3ExistingBC12Existing
4ExistingCC123Existing
5ExistingDD12Existing
6NewBC12Existing
7NewCC123Existing
8New
DD1234New

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

9 Replies
sunny_talwar

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
Creator II
Creator II
Author

Hi Sunny,

Thanks for your scripts, It works.

Thanks

Kumar KVP

sunny_talwar

Super

pradosh_thakur
Master II
Master II

Hi stalwar1

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

Learning never stops.
sunny_talwar

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

pradosh_thakur
Master II
Master II

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

Learning never stops.
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
Master II
Master II

my bad .. got it .. thanks ..

Learning never stops.