Extract number from string

[muhammadraza]

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

[MarcoWedel]

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

[#3]

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()

[tobias_klett]

Hi Muhammad,

check my SOLution. 🙂

Regards Tobias

[muhammadraza]

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

[muhammadraza]

@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

[anbu1984 · Accepted solution]

Check this qvw

[maxgro]

you can join the 2 tables and then use wildmatch

a:

LOAD AccountNo, AccountType

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

g:

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;