Intervalmatch range of values ( values that contains another values)

[marcel_olmo]

Hey guys, I'm trying to do a typical intervalmatch between a range of numbers. But the particularity is that I'm comparing the range with values that are smaller parts of the range.

Here is the explanation :

As you can see in the first row of the Main Table ( 2.A1.I.1) , there is a minimum range (10000000) and a maximum range (10099999), and it only matches the account 10000001 beucase it has the same length of the range.

But there is some other accounts that there are in the range but they don't have the same length, so intervalmatch considers they're not in the range (100, 1000,1001, etc).

Anybody know how can I do some kind of left() function of the first 3 or 4 numbers in the intervalmatch of the range account to fit correctly all the accounts?

By now, this is my code :

RangeAccountsTable :

LOAD N_Fila2 as Row,
N_Fila_Cuenta_Min as MinRange,
N_Fila_Cuenta_Max as MaxRange
FROM
[RangeAccounts.qvd]
(qvd);

IdAccountsTable :

LOAD N_Fila_Cuenta_Aux as MatchedAccount
FROM
[IdAccount.qvd]
(qvd);

LEFT JOIN (RangeAccounts)
INTERVALMATCH (MatchedAccount)
LOAD MinRange, MaxRange
RESIDENT RangeAccounts;

I hope you understand what I mean and I'm trying to do.

I've attached a zip file with the tables and the QV if somebody wants to see it by himself.

Thank you very much in advance!!

[#2]

Hi Marcel,

Here you are a possible solution.

I hope it helps you.

Regards

[marcel_olmo]

Thank you for yor reply VivaChetos.

You've had a good idea , but incomplete.

The point is that an account 100 is different than another one of 10000, and if I add as zeros as the rest of the longitude I will not have a way to distinguish between the two account.

I've been trying it using a mapping table adding '-' to separate the additional zeros with a subfield at the end. But at least, I have the last value.

Here I give you an example of what I mean :

I've selected the account 10000000, which has two different origin accounts : 100 and 1000000.

I've done a mapping table to distinguish between the two origins adding a '-' between the original account and the zeros added. So the result is :

100-00000 and 1000000-0.

The problem is when I try to apply the applymap method it only takes the last value found. As you can see in the picture :

My desired result would be the main table with the two rows perfectly filled (now the row of the Matched Account 100, which belongs to the range 10000000 / 10099999 is void).

How can I solve that ?

I've attached the file if someone wants to see it properly.

Many thanks in advance!

[marcel_olmo · Accepted solution]

Finally I got it doing two mapping tables of the IdTables to combine this results. First I do a mapping of MatchedAccount2 to MatchedAccount3 to distinguish between possible matches (doing an unique ID), and after that I do another mapping of MatchedAccount3 to MatchedAccount to get my desired result.

If someone is interested, I've uploaded the solution in a file.

See you around!!