Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
nevilledhamsiri
Specialist
Specialist

How to extract codes & names from a long list of data array with a different lengths

Hi,

Given below is a sample  of data loaded under CREDITOR_CODE which are having varying length as I have counted in excel. I have shown only a part of data & the list contains more than 100 records with different length in each row. Can our expert propose an expression to extract codes & names separately?

Thanks neville

  

CREDITOR _CODELEN
CREDITOR CODE  127524 M.P.PATHIRANA37
CREDITOR 127454  S.SIVAKUMARA30
CREDITOR CODE 52514-011 SEYLAN BANK35
CREDITOR CODE 145214_125  COMMERCAL BANK40
CREDITOR 1452142  COMMERCCAI CREDIT36
CREDITOR 124114-011 PAN ASIA BANK34
I need to extract all numbers given in each row as creditor codes
I need to extract the creditor names which are on the right side of each row
1 Solution

Accepted Solutions
sunny_talwar

Another option

Base:

LOAD *,

Mid(CREDITOR_CODE, FindOneOf(CREDITOR_CODE, '0123456789')) as [creditor names];

LOAD * INLINE [

    CREDITOR_CODE, LEN,

    CREDITOR CODE  127524 M.P.PATHIRANA, 37,

    CREDITOR 127454  S.SIVAKUMARA, 30,

    CREDITOR CODE 52514-011 SEYLAN BANK, 35,

    CREDITOR CODE 145214_125  COMMERCAL BANK, 40,

    CREDITOR 1452142  COMMERCCAI CREDIT, 36,

    CREDITOR 124114-011 PAN ASIA BANK, 34

];

View solution in original post

2 Replies
Clever_Anjos
Employee
Employee

Please try this

Base:

LOAD * INLINE [

    CREDITOR_CODE, LEN,

    CREDITOR CODE  127524 M.P.PATHIRANA, 37,

    CREDITOR 127454  S.SIVAKUMARA, 30,

    CREDITOR CODE 52514-011 SEYLAN BANK, 35,

    CREDITOR CODE 145214_125  COMMERCAL BANK, 40,

    CREDITOR 1452142  COMMERCCAI CREDIT, 36,

    CREDITOR 124114-011 PAN ASIA BANK, 34

];

load

CREDITOR_CODE as FULL_LINE,

[creditor codes],

Trim(Mid(CREDITOR_CODE,Index(CREDITOR_CODE,[creditor codes])+Len([creditor codes]))) as [creditor names];

load

CREDITOR_CODE,

KeepChar(CREDITOR_CODE,'0123456789-_') as [creditor codes]

Resident Base;

Drop Table Base;

sunny_talwar

Another option

Base:

LOAD *,

Mid(CREDITOR_CODE, FindOneOf(CREDITOR_CODE, '0123456789')) as [creditor names];

LOAD * INLINE [

    CREDITOR_CODE, LEN,

    CREDITOR CODE  127524 M.P.PATHIRANA, 37,

    CREDITOR 127454  S.SIVAKUMARA, 30,

    CREDITOR CODE 52514-011 SEYLAN BANK, 35,

    CREDITOR CODE 145214_125  COMMERCAL BANK, 40,

    CREDITOR 1452142  COMMERCCAI CREDIT, 36,

    CREDITOR 124114-011 PAN ASIA BANK, 34

];

View solution in original post