topic Re: Extract number from string in QlikView

Extract number from string

muhammadraza — Mon, 06 Oct 2014 21:41:59 GMT

Hi Guys,

I want to connect these two tables by account number column

Table 1

Table 2

Currently there is no relation between these two tables because in the 2nd table the account number is hidden inside the GLHDescription column.

What I want is that I want to extract this account number from GLHDescription column and add this in Table2 so that the relation could be created between these two tables.

Please let me know how can I achieve this.

Regards,

Muhammad Raza

Re: Extract number from string

MarcoWedel — Mon, 06 Oct 2014 21:54:04 GMT

Hi,

one solution could be:

table1:
LOAD *
FROM [http://community.qlik.com/servlet/JiveServlet/download/626902-130622/AccountDetails.xlsx] (ooxml, embedded labels, table is Sheet1);


table2:
LOAD *,
     KeepChar(SubField(GLHDescription,'Min.',1),'0123456789') as AccountNo
FROM [http://community.qlik.com/servlet/JiveServlet/download/626902-130621/GLH.xlsx] (ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco

Re: Extract number from string

Mon, 06 Oct 2014 21:56:22 GMT

Hi Muhammad

in the load of Table 2 use

Keepchar(GLHDescription,'1234567890') as Accountno.

then, in this way both will be joined.

this command will help you Keepchar()

Re: Extract number from string

tobias_klett — Mon, 06 Oct 2014 22:16:26 GMT

Hi Muhammad,

check my SOLution. 🙂

Regards Tobias

Re: Re: Extract number from string

muhammadraza — Tue, 07 Oct 2014 09:00:35 GMT

Actually the requirement is more tricky, I cannot count on '1000' and 'Min' strings, PFA is the possible list of scenarios of string.

Re: Extract number from string

muhammadraza — Tue, 07 Oct 2014 09:07:54 GMT

@Tobias Klett @Marco Wedel

Thanks guys for your replies but actually the requirement is more tricky, I cannot count on '1000' and 'Min' strings appearance, PFA is the possible list of scenarios of string in 'GLHDescription'

Regards,

Muhammad Raza

Re: Re: Re: Extract number from string

anbu1984 — Tue, 07 Oct 2014 09:16:50 GMT

Check this qvw

Re: Extract number from string

maxgro — Tue, 07 Oct 2014 09:29:32 GMT

you can join the 2 tables and then use wildmatch

LOAD AccountNo, AccountType

FROM AccountDetails2.xlsx (ooxml, embedded labels, table is Sheet1);

load RowNo() as id, *;

LOAD GLNo, LedgerNo, GLDescription, GLHistoryPTID, GLHOriginTN, GLHistoryAmount, GLTC, GLHDescription, GLHRefence, GLHCreateDate, EmployeeID, RSMName, CurrencyID, Currency, BranchNo, BranchName, Q, R, S, T, U

FROM GLH2.xlsx (ooxml, embedded labels, table is Sheet1);

tmp:

load id, GLHDescription Resident g;

join (tmp) load * Resident a;

final:

NoConcatenate

LOAD

if(wildmatch(GLHDescription, '*' & AccountNo & '*'), 1, 0) as FlagMatch

Resident tmp;

DROP Table tmp;