topic Re: Remove data from the loaded field in QlikView

Remove data from the loaded field

Wed, 03 Sep 2014 16:00:46 GMT

Hi guys,

let say I have list of the Delivery Companies in the database. With some of them are not working any more or changed the name, so I want to remove them or rename. So renaming works fine when I use ApplyMap but it fail when I wanted it to remove them as it just replace data with blank line.

DLMap:

MAPPING LOAD * INLINE [

DL, newDL

Sony,

Samsung,

JVC,JVC Inc.

];

then

LOAD

ApplyMap('DLMap', DL_DESC) as DL_DESC

FROM

xxxxx

This will rename JVC to JVC Inc., but Sony and Samsung are replaced with blank data.

Is there is any other way to remove them from being loaded?

Thank you

Re: Remove data from the loaded field

alexandros17 — Wed, 03 Sep 2014 16:11:13 GMT

you need a condition in the where:

...

where wildmatch(DL, 'Samsung, 'Sony' ...) = 0;

let me know

Re: Remove data from the loaded field

Anonymous — Wed, 03 Sep 2014 16:13:12 GMT

hi,

if you have a field which contains only the firms you want to keep in the records or which firms you dont want in the records you can work with exists/not exists

Modify your script the following way:

1. Remove Sony and Samsung from Mapping

2. within applymap set Null() as default value (if you want to use exists, otherwise pass original field as default value)

DLMap:

MAPPING LOAD * INLINE [

DL, newDL

// Sony,

// Samsung,

JVC,JVC Inc.

];

then

LOAD

ApplyMap('DLMap', DL_DESC, Null()) as DL_DESC

FROM

xxxxx where exists (DL_DESC, ApplyMap('DLMap', DL_DESC, Null()) ;

use the field which contains all records you want to keep in the exists clause. If you have a field containing the records you want to exclude use "not exits( Field, ApplyMap('DLMap', DL_DESC, Null())" instead.

- Stefan

Re: Remove data from the loaded field

maxgro — Wed, 03 Sep 2014 16:17:27 GMT

add a load (bold)

DLMap:

MAPPING LOAD * INLINE [

DL, newDL

Sony,

Samsung,

JVC,JVC Inc.

];

load * where len(trim(DL_DESC))>0;

LOAD

ApplyMap('DLMap', DL_DESC) as DL_DESC

FROM

xxxxx

Re: Remove data from the loaded field

Thu, 04 Sep 2014 08:13:41 GMT

@ Alessandro what the wildmatch? and where to add this condition, could you paste whole part of screept?

Re: Remove data from the loaded field

Thu, 04 Sep 2014 08:15:44 GMT

Thank you, but the solution proposed by Massimo looks simplier.

Any way I will try your solution as well to learn how to use it.

Re: Remove data from the loaded field

alexandros17 — Thu, 04 Sep 2014 08:16:18 GMT

it behaves as Like in sql so

wildmatch(DL, 'Samsung, 'Sony' ...) = 0 means that if the field DL contain the string 'Samsung' the function returns 1, if DL contains Sony, the functions return 2 and so on