Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Extract number from text field

hi in the following excel applikation i wish to extract only the last four numbers off each row.

if a row is lacking the four numbers (companycode) i wish not to read them.

i have tried trim function without luck as each of the rows have different lengths;

any suggestions?

Best,

Brad

1 Solution

Accepted Solutions
MVP
MVP

Re: Extract number from text field

Try something like

Company:

LOAD

     Company,

     if(isnum(right(Company,4)),right(Company,4)) as Company_num,

FROM

CompanyList.xlsx

(ooxml, embedded labels, table is Sheet1);

or if you only want to read these companies:

Company:

LOAD

     Company,

     right(Company,4) as Company_num

FROM

CompanyList.xlsx

(ooxml, embedded labels, table is Sheet1)

where isnum(right(Company,4));

3 Replies

Re: Extract number from text field

I suggest you attach the excel file.


talk is cheap, supply exceeds demand
Not applicable

Re: Extract number from text field

Thanks i have attached the excel sheet

MVP
MVP

Re: Extract number from text field

Try something like

Company:

LOAD

     Company,

     if(isnum(right(Company,4)),right(Company,4)) as Company_num,

FROM

CompanyList.xlsx

(ooxml, embedded labels, table is Sheet1);

or if you only want to read these companies:

Company:

LOAD

     Company,

     right(Company,4) as Company_num

FROM

CompanyList.xlsx

(ooxml, embedded labels, table is Sheet1)

where isnum(right(Company,4));

Community Browser