Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Check this qvw
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
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()
Hi Muhammad,
check my SOLution. 🙂
Regards Tobias
Actually the requirement is more tricky, I cannot count on '1000' and 'Min' strings, PFA is the possible list of scenarios of string.
@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
Check this qvw
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;