topic Re: String Functions in QlikView

String Functions

akpofureenughwu — Sun, 26 Feb 2017 08:12:10 GMT

I have a field that contains staff names and ID. I would love to seperate them so I can have a column for name and another for id...

I used the right('ID', 5) as IDNO.... but it is not giving the desired format...

Please script below and attached picture

LIB CONNECT TO 'UGOCHI HR';

LOAD ID,

Right( ID, 5) as IDNO,

"NAME_OF_EMPLOYEE",

DEPARTMENT,

"LOAN_START_DATE",

"REPAYMENT_START_DATE",

"EXPECTED_LOAN_END_DATE",

"LOAN_AMOUNT_GRANTED",

"INSTALLMENT_AMOUNT",

"LOAN_AMOUNT_PAID",

"OUTSTANDING_BALANCE",

''''''''''''''''''''''''''''''''...............................................

Re: String Functions

shivkumar300 — Mon, 27 Feb 2017 09:14:29 GMT

you can mention the below Right function and mention the number accordingly.Please find the below example

data:
LOAD* inline [
ID
AAAA12333
AAAA12334
AAAA12335
AAAA12336
AAAA12337
AAAA12338
AAAA12339
AAAA12340
AAAA12341
AAAA12342
AAAA12343
AAAA12344
];

dat1:
load right ("ID",5) as IDNo ----> use this

Resident data;
DROP Table data;

Re: String Functions

rahulpawarb — Mon, 27 Feb 2017 09:31:50 GMT

Hello Akpofure,

Trust that you are doing great!

Please refer below sample script:

Data:
LOAD
ID,
REPLACE(ID, RIGHT(ID,5), ' ') AS StaffName,
RIGHT(ID,5) AS StaffId;
LOAD * INLINE [
ID
Abanum Odikayor19145
Abayomi Daniel90536
Abdel Mohammed18235
Abdulgafar Zakariyau18479
Abdulkadir Yusuf20638
Abdulkarim Braimoh74235
];

Hope this will be helpful.

If you are still facing challenges then please share the application with sample data.

P.S.: This script is designed considering StaffId is of 5 character length.

Regards!

Rahul

Re: String Functions

sunny_talwar — Mon, 27 Feb 2017 10:45:41 GMT

May be try one of these:

Right(Trim("ID"), 5) as IDNO

KeepChar("ID", '1234567890') as IDNO