topic Re: Compare the currrent row and previous row in QlikView

Compare the currrent row and previous row

kumarkp412 — Fri, 29 Dec 2017 13:13:01 GMT

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

Re: Compare the currrent row and previous row

sunny_talwar — Fri, 29 Dec 2017 13:40:10 GMT

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;

Re: Compare the currrent row and previous row

kumarkp412 — Fri, 29 Dec 2017 14:06:20 GMT

Hi Sunny,

Thanks for your scripts, It works.

Thanks

Kumar KVP

Re: Compare the currrent row and previous row

sunny_talwar — Fri, 29 Dec 2017 14:11:16 GMT

Super

Re: Compare the currrent row and previous row

pradosh_thakur — Fri, 29 Dec 2017 14:14:33 GMT

Hi stalwar1‌

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

Re: Compare the currrent row and previous row

sunny_talwar — Fri, 29 Dec 2017 14:15:54 GMT

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

Re: Compare the currrent row and previous row

pradosh_thakur — Fri, 29 Dec 2017 14:18:54 GMT

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

Re: Compare the currrent row and previous row

sunny_talwar — Fri, 29 Dec 2017 14:27:40 GMT

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

Re: Compare the currrent row and previous row

sunny_talwar — Fri, 29 Dec 2017 14:28:57 GMT

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

Re: Compare the currrent row and previous row

pradosh_thakur — Fri, 29 Dec 2017 14:34:00 GMT

my bad .. got it .. thanks ..