topic Re: link two tables in App Development

link two tables

ali_hijazi — Mon, 19 Dec 2022 10:12:22 GMT

Hello
I'm working in Qlik Sense
I got two tables T1 and T2
T1 has column named Account and contains for example 2 values 7010100 and 7010200
T2 also has a column named Account but values are like this 7010x00 for example

so I want to translate the values of T1 to match the values of T2 so that rows link together
the difficulty is the x position is not always before the last 2 digits

Kindly advise

Re: link two tables

Or — Mon, 19 Dec 2022 10:26:17 GMT

Perhaps something like:

Load Replace(Account,'x',IterNo()-1) as Account

From T2

While IterNo() <= 10;

Re: link two tables

Gang — Mon, 19 Dec 2022 10:31:54 GMT

You can use PurgeChar Or KeepChar function to achieve this.

PurgeChar ( T2, 'x')

KeepChar ( T2, '1234567890')

Re: link two tables

ali_hijazi — Tue, 20 Dec 2022 09:05:52 GMT

Hi @Gang
i don't want to remove the x
I want to replace it by numbers from 0->9 and then match the result with teh really existing accounts
another thing is that I might have more than one x like
651xxxx0 or xxxx1

xxxx1 should match 11101 (real account)

Re: link two tables

ali_hijazi — Tue, 20 Dec 2022 09:30:34 GMT

Hello @Or
your suggestion works only when there is one x
for example 651x00 gives all possible combinations 651000, 651100, etc...
however sometimes I have the following:
xxxx1 with you script the result is 11111,00001,22221,33331,....99991
where as I want to have all possible combinations like 00001,00011,00021,etc...
and sometimes I might have 651xx00

so when there is only one x in the account , your script works fine but when there is more than one this script fails

Re: link two tables

AronC — Tue, 20 Dec 2022 10:07:40 GMT

@ali_hijazi I think you could solve it as below. (Psedocode so you might need to modify)
Make three fields as
T2:
subfield(Account ,'x',1) as AccPart1,
keepchar(Account,'x') as AccPartX,
subfield(Account ,'x',-1) as AccPart3,

left join(T2)
load
repeat('x',len(iterno())) as AccPartX,
iterno() as NewPart2
autogenerate(1)
while iterno() <10000;

newT2:
load
AccPart1 & NewPart2 & AccPart3 as Account
resident T2;

Re: link two tables

Or — Tue, 20 Dec 2022 10:37:01 GMT

Would have been good to lead with that...

With an unknown number of wildcards and an unknown number of combinations, I'm not sure how to go about doing this except by looping through all the possible iterations:

* Count the number of 'x's

* Iterate 10^Count(x) times

* Replace the 'x's based on the iteration number, using the digits of iterno, e.g. on the 158th iteration x0xx0 will be replaced with 10580

This shouldn't be too hard to do if the length of the string is known in advance, but if it isn't, you'll probably have to loop through the iterations of replacing each 'x' as well.

Re: link two tables

ali_hijazi — Wed, 21 Dec 2022 06:45:36 GMT

Hello
Managed to do this using the LevensteinDist function

X_Account:

Load * Inline [

XAccount

651x000

651x001

xxxx1

65xxx00

];

Account:

Load * inline [

AccountID,AccountName

6511000,A1

6519000,A2

6511001,A3

651100,A4

01011,A5

02211,A6

02212,A7

];

join(Account)

load * resident X_Account;

drop table X_Account;

tempAccount:

Load *

where LevenshteinDistF=Xcount;

load *, SubStringCount(XAccount,'x') as Xcount, LevenshteinDist(XAccount,AccountID) as LevenshteinDistF

resident Account;

drop table Account;