Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Script: Rename a value from a field at LOAD

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

 

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

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

Hi

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

 

View solution in original post

2 Replies
Highlighted
Creator II
Creator II

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

Hi

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

 

View solution in original post

Highlighted
Creator
Creator

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

Thank you, the method 1 works perfectly !


Best regards

 

Fabien