Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
muhammadraza
Partner
Partner

Extract number from string

Hi Guys,

I want to connect these two tables by account number column

Table 1

1.png

Table 2

2.png

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

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

7 Replies
MarcoWedel

Hi,

one solution could be:

QlikCommunity_Thread_136588_Pic2.JPG.jpg

QlikCommunity_Thread_136588_Pic1.JPG.jpg

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

Not applicable

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

Hi Muhammad,

check my SOLution. 🙂

Regards Tobias

muhammadraza
Partner
Partner

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

muhammadraza
Partner
Partner

@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
Master III
Master III

maxgro
MVP
MVP

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;