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

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

nevilledhamsiri — Wed, 25 Nov 2020 16:16:04 GMT

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 _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


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

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

Clever_Anjos — Tue, 29 Aug 2017 18:40:33 GMT

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;

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

sunny_talwar — Thu, 31 Aug 2017 12:35:02 GMT

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

];