topic Re: Script: Rename a value from a field at LOAD in QlikView

Script: Rename a value from a field at LOAD

fgirardin — Sat, 16 Nov 2024 02:05:41 GMT

Hello,

I use data from our ERP to make tables. The script used is:

LOAD

"FIELD1",
"FIELD2",
"FIELD3";

SQL Select *
FROM STR.db."ITEMS"
Where COMPANY = 'RSM';

I have a field in particular that contains 2 values that I'd like to rename as the same

Field = "Item_Status" -- Values available are: ACT, OBS, BOMOBS

I would like to replace all values "BOMOBS" by "OBS"

What would be the best way to do it in my script ?

Thank you for your help

Re: Script: Rename a value from a field at LOAD

atoz1158 — Tue, 29 Oct 2019 15:35:46 GMT

There are a few ways to do this below are two methods, method 1 is the quick and easy way if this is going to be the only value changed, method 2 allows for expansion of the number of values in that field to be changed just by adding them to the "Mapping" load. I have assumed that Field2 is the field required to be changed in my examples but you can change that as required.

Method 1

LOAD

FIELD1,
IF(FIELD2='BOMOBS','OBS',FIELD2) AS FIELD2,
FIELD3;

SQL Select *
FROM STR.db."ITEMS"
Where COMPANY = 'RSM';

Method 2

Item_Stat_MAP:
MAPPING LOAD * INLINE [
Was, Shouldbe
BOMOBS, OBS
];

LOAD

FIELD1,
ApplyMap('Item_Stat_MAP',FIELD2) AS FIELD2,
FIELD3;

SQL Select *
FROM STR.db."ITEMS"
Where COMPANY = 'RSM';

Regards

Adrian

Re: Script: Rename a value from a field at LOAD

fgirardin — Tue, 29 Oct 2019 15:40:40 GMT

Thank you, the method 1 works perfectly !

Best regards

Fabien