Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

kumarkp412
Contributor 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
MVP
MVP

Re: Compare the currrent row and previous row

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;

9 Replies
MVP
MVP

Re: Compare the currrent row and previous row

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
Contributor II

Re: Compare the currrent row and previous row

Hi Sunny,

Thanks for your scripts, It works.

Thanks

Kumar KVP

MVP
MVP

Re: Compare the currrent row and previous row

Super

pradosh_thakur
Valued Contributor III

Re: Compare the currrent row and previous row

Hi stalwar1

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

MVP
MVP

Re: Compare the currrent row and previous row

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

pradosh_thakur
Valued Contributor III

Re: Compare the currrent row and previous row

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

MVP
MVP

Re: Compare the currrent row and previous row

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....

MVP
MVP

Re: Compare the currrent row and previous row

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
Valued Contributor III

Re: Compare the currrent row and previous row

my bad .. got it .. thanks ..