Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Remove data from the loaded field

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Remove data from the loaded field

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

6 Replies

Re: Remove data from the loaded field

you need a condition in the where:

...

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

let me know

s_kunte23
Contributor III

Re: Remove data from the loaded field

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

MVP
MVP

Re: Remove data from the loaded field

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

Not applicable

Re: Remove data from the loaded field

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

Not applicable

Re: Remove data from the loaded field

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

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

Community Browser