How to merge list box values into one value?

[#1]

Hi,

I have values in list box as below for Status column.

Status

AA

GIO

POI

FF

RR

Accounts

Sales

Merchants

Audits

Students

how to write condition to load

Status

Accounts

Sales

Merchants

Audits

Students

into one value named as Status_Left. so, then Status_Left should left with below

Status

AA

GIO

POI

FF

RR

Status_Left

please can anyone suggest me how to do this?

Thanks.

[Anonymous]

if the length of your abbreviations is always 3 or less and if always the lenght for Status_left is greater than 3 you can use

TEST:

Noconcatenate load

if (len(Status)>3, 'Status_Left', Status) as Status

resident Status;

drop table Status;

rename table TEST to Status;

if not you Need to use match

if (match(Status,'Accounts, Students',.., Status_left, Status) as Status

[saurabh5]

hi Amelia,

another option would be to create an inline table and use the new field:-

load * Inline [
Status, Modified_Status
AA, AA
GIO, GIO
POI, POI
FF, FF
RR, RR
Accounts, Status_Left
Sales, Status_Left
Merchants, Status_Left
Audits, Status_Left
Students, Status_Left
];

[#4]

Thanks. some of the values length is more than 3. what if I have 1000 values in list box? how to do this at one go instead of writing expression for all