Delete data whose key exists more than once

Fri, 09 Jul 2010 13:29:37 GMT

Hi,

I need to clear a table so that every key "Material_Period" exists just once.

Distinct does not work since other fields are different.

where not exists brought errors.

Any idea?

Plant	% period	% Material	Cost
1	1	300	100
2	1	300	120
1	1	301	80
1	2	300	110
2	2	303	90

Right now I'm doing a detour like that:

Kalk_Temp:

LOAD

PLANT

replace

(ltrim(replace(MATERIAL, '0', ' ')), ' ', 0) as MATERIAL

makedate

(left(SPBUP,4),right(SPBUP,2)) as Datum

replace

(ltrim(replace(MATERIAL, '0', ' ')), ' ', 0)&'_'&SPBUP as Key

// LOTSIZE,

// LOTSIZE_UNIT,

ZHK

ZMAT

FROM

U:\ZKALKMAT.qvd

(

qvd

)

where

SPBUP >

201003

and

VRSIO =

and

right(MATERIAL,6) >

315000

;

qualify

unqualify

'Key';

Kalk0001:

LOAD

resident

Kalk_Temp

where

PLANT = 0001

;

Kalk0003:

LOAD

resident

Kalk_Temp

where

PLANT <>0001

;

drop

table

Kalk_Temp;

unqualify

Kalkfinal:

Load

resident

Kalk0001;

Load

resident

Kalk0003

where

not exists (Key)

;

Thanks for your help!

topic Delete data whose key exists more than once in QlikView

Delete data whose key exists more than once