topic Eliminate similar rows in QlikView

Eliminate similar rows

Mon, 28 Jul 2014 19:50:40 GMT

I have a scenario where there are multiple rows pertaining to the same account due to user input error. I am wondering if it is possible to only keep one of these rows for each account. Obviously the distinct function in qlikview seems like a solution to this, the only problem is that these similar rows differ slightly by 1 or 2 fields - so distinct doesn't work. I don't really care which of the rows stay, I just want to be able to identify which rows share [Field1] and [Field2] and if there is more than one to only keep one. Thank you.

Re: Eliminate similar rows

maxgro — Mon, 28 Jul 2014 20:09:51 GMT

if I understand your req

source:

// add an id

load *, rowno() as id;

// your 2 fields

load * inline [

field1, field2

1,a

1,b

1,c

2,a

2,b

1,a

2,a

3,a

3,b

];

// group by and join to keep only the max (or min) by field1 and field2

right join (source)

load field1, field2, max(id) as id

Resident source

group by field1,field2;

Re: Eliminate similar rows

maxgro — Mon, 28 Jul 2014 20:16:09 GMT

or (it seems easier)

source:

load * inline [

field1, field2, f3, f4

0,a,1,1

0,b,1,1

1,a, 10, 10

1,b, 20, 20

1,c,60,60

2,a, 10, 10

2,b,50,50

1,a, 25, 25

1,a, 10, 10

2,a, 22, 22

3,a, 21, 21

3,b, 20, 20

];

NoConcatenate load *

Resident source

where field1<> Peek(field1) or field2<>Peek(field2)

order by field1, field2;

DROP Table source;