Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
you need a condition in the where:
...
where wildmatch(DL, 'Samsung, 'Sony' ...) = 0;
let me know
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
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
@ Alessandro what the wildmatch? and where to add this condition, could you paste whole part of screept?
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.
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