topic Re: Merge rows with common fields and verifying conditions in QlikView

Merge rows with common fields and verifying conditions

Mon, 30 Dec 2013 20:16:56 GMT

I wish to write a clean up script that merges fields that verify

1/ fields A and B are common to both rows

2/ fields C and D are N/A for one row and equals field B for the other row

Ex.

A B C D

Val1 Val2 N/A N/A
Val1 Val2 Val2 Val2

then in the first row, fields C and D should be filled with value Val2.

I cannot find out how to write a condition on two rows (or write a condition with a search on the other rows inside)

Thanks

Re: Merge rows with common fields and verifying conditions

maxgro — Mon, 30 Dec 2013 20:43:24 GMT

for 2 rows you can try something similar to

Tmp:

LOAD * INLINE [

A , B , C , D,

Val1, Val2, N/A, N/A

Val1, Val2 , Val2, Val2

];

Table:

load

if(Peek(A)=A and Peek(B)=B and C='N/A' and D='N/A' and peek(C)=Peek(B) and peek(D)=Peek(B), Peek(C), C) as NewC,

if(Peek(A)=A and Peek(B)=B and C='N/A' and D='N/A' and peek(C)=Peek(B) and peek(D)=Peek(B), Peek(D), D) as NewD

resident Tmp

order by A, B, C desc, D desc;

DROP Table Tmp;

but what if you have more than 2 rows? example

A B C D

Val1 Val2 N/A N/A
Val1 Val2 Val2 Val2

Val1 Val2 N/A N/A

which result do you want?

Re: Merge rows with common fields and verifying conditions

Mon, 30 Dec 2013 20:48:57 GMT

thanks, this is hepful.

then, i would like all three rows be replaced when needed by Val1 Val2 Val2 Val2

Re: Merge rows with common fields and verifying conditions

maxgro — Mon, 30 Dec 2013 20:55:51 GMT

Try this;

perhaps you have to change N/A in something else to be sure the order by works

Tmp:

LOAD * INLINE [

A , B , C , D,

Val1, Val2, N/A, N/A

Val1, Val2 , Val2, Val2

Val1, Val2, N/A, N/A

Val1, Val3, N/A, N/A

Val1, Val3 , Val3, Val3

Val1, Val3, N/A, N/A

];

Table:

noconcatenate load

rowno(),

if(Peek(A)=A and Peek(B)=B and C='N/A' and D='N/A' and peek(C)=Peek(B) and peek(D)=Peek(B), Peek(C), C) as C,

if(Peek(A)=A and Peek(B)=B and C='N/A' and D='N/A' and peek(C)=Peek(B) and peek(D)=Peek(B), Peek(D), D) as D

resident Tmp

order by A, B, C desc, D desc;

DROP Table Tmp;