topic Dropping records from a resident table in QlikView

Dropping records from a resident table

Fri, 29 May 2015 15:24:43 GMT

I have to create two resident tables

Table 1

field a as key

field b as skey1

field c

field d

The above table has all material numbers

Table 2

field a as key

field b as skey1

field e

So these are two resident table. What I need to do is if the Table 2 Key record exist in table 1,

I want to delete the table 1 record and use the table 2.

So example there are 43 Material records in Table 1

But the join for Table two checks an additional table and if the material does not exit in this table it does not

pick it up and then goes and gets values from additional tables. So my table 2 will have on 25 records.

So I want to delete the 25 records out of Table 1. So my end result is 43 records.

Diane

Re: Dropping records from a resident table

alexandros17 — Fri, 29 May 2015 15:33:06 GMT

do this:

Load

field a as key,

field b as skey1,

field a & field b as KEY

field e

resident Table 2

Load

field a as key

field b as skey1

field a & field b as KEY

field c

field d

resident Table 1 where not exists (KEY,KEY)

Re: Dropping records from a resident table

Fri, 29 May 2015 16:03:41 GMT

I have the below code and it is telling me that my %KEY field is not found.

What am I missing.

NoConcatenate

Load

%MaterialNumber_Key & %Classification_Key & Characteristics as %KEY,

resident CLASSCHAR1;

Load

%MaterialNumber_Key & %Classification_Key & Characteristics as %KEY,

resident CLASSCHAR where not exists(%KEY,%KEY);

Hittite Microwave is now Analog Devices. Learn more at www.analog.com/hittite<http://www.analog.com/hittite>.

Diane Fenton-Papia

Business System Developer

Analog Devices, Inc.

1 Technology Way

Norwood, MA 02062

Phone: 781-461-3570

mailto:diane.papia@analog.com

Re: Dropping records from a resident table

maxgro — Fri, 29 May 2015 16:57:33 GMT

try this

//resident CLASSCHAR where not exists(%KEY,%KEY);

resident CLASSCHAR where not exists(%KEY, %MaterialNumber_Key & %Classification_Key & Characteristics);

Re: Dropping records from a resident table

Fri, 29 May 2015 17:58:17 GMT

This was great thank you so much for the help.

Hittite Microwave is now Analog Devices. Learn more at www.analog.com/hittite<http://www.analog.com/hittite>.

Diane Fenton-Papia

Business System Developer

Analog Devices, Inc.

1 Technology Way

Norwood, MA 02062

Phone: 781-461-3570

mailto:diane.papia@analog.com