Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. 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