Intervalmatch using strings

[marcel_olmo]

Hey, I just want to ask if is it possible to do an intervalmatch between strings. I've done many times with numbers, and I have no problems at all.

But now I have to do it with strings, and it doesn't work at all.

I have this two tables :

In this case apparently it has no sense to do an intervalmatch because the min and max value are always the same, but in the future they will be the first and the last value of a range of strings.

My desired result is to get a final table with two fields : The father field, and the son field.

I've build an excel with my desired result :

In addition, I've uploaded a zip file that contains everything (the qv document and 2 qvd's, cuentas and result) with a extraction of what I'm trying to do. (What's not working is the commented code ).

PS : You can reload the document all the times that would be necessary.

Many thanks by advance!!!

[pover]

Do you need to use intervalmatch function or the hierarchy function?

Concerning the question about the intervalmatch with string values, you can in this case separate the letter from the the number. With a subfield(max,'.',1), you extract the letter given that max and min havd the same letter and then a subfield(min,'.',2) and a subfield(max,'.',2) you can extract the range to use in the intervalmatch.

However to get the table that you want, you should use the hierarchy() or hierarchybelongsto() function.

Saludos.

[marcel_olmo]

Thanks Karl for your answer, this is really breaking my head.

Yes, in fact, my purpose is to build a hierarchy of father rows and son rows for a financial accounts scheme.

Until now we had done this with numbers, and it was easily to solve it with intervalmatch() following the example of Qlikview help (hours example).

But from now on, my company decided to include the possibility to add alphanumeric numbers to refer the scheme account rows.

The point is : Are you telling me that I cannot use intervalmatch() with strings, and this is the reason that when I try to do it, my results are void. Isn't it?

I don't think that the solution is with subfields, because maybe the minimum and maximum value would be differents and you're gonna need to get the range as well.

I think that maybe the Hierarchy solution is the best, but I don't know how to use Hierarchy function if I have to get the range of numbers that belongs to each "father row".

Could you please tell me an example of how can I use Hierarchy in this case ??

Many thanks in advance !!

[Miguel_Angel_Baeyens]

Hello Marcel,

You cannot use intervalmatch() with non numeric values. Anyway, you can assign to any of those rows you have another field as number (say using autogenerate) and then use intervalmatch with those numeric fields. Then you will get your tree with numerical values underlying, and text values to be displayed on charts.

Hope that helps.

[marcel_olmo]

Thank you Miguel for your quick answer.

So, do you mean I have to create a new column with an autogenerate function to parse it as if they were numbers, isn't it?

But if I want to do it, I have to create an autogenerate number column in both tables (base table and IntervalMatch table), isn't it?

How could I get an unique autogenerated number from a string that recognizes that?

Maybe there's a function to parse a string to number parsing the letters of string as numbers, or something like that.

Do you know what I mean?

Many Thanks by advance!!!

[pover]

You might be able to map the charaters A,B,C,etc. to numeric value that depending on the range of you numbers could be A -> 1000, B-> 2000, C->3000 where A.1 = 1001, A.2=1002, etc. Then A.TOT would be the range between 1001 and 1011 and B.TOT would be between 2011 and 2016. With the autogenerate you would create a mapping table that would later map the first subfield (letter) and then add the second subfield (number) to create the necessary values to do an intervalmatch.

Anyway, here's an example of hierarchy, but if you just use hierarchy, you have to assign each descrete value (not a range of values) to its parent which is alot of manual work. The best solution would be to combine intervalmatch with hierarchy.

Regards.

[marcel_olmo · Accepted solution]

Hey guys! Finally I got the solution!

What I've done is the following :

1- Load Base table and the Intervalmatch Table (in this table I've added an autonumber).

2 - I've done 2 mapping load tables of the IntervalMatch Table (because the order of the fields matters).

3- I've done an applymap of the minimum and maximum values of the Base Table the get the Minimum and the Maximum value in "numeric format".

4- I've done a join between the two tables to get all the values of the range of Base Table in the Intervalmatch Table.

5- Finally, I've done a last applymap to get the string value of all the numbers of the range I've calculated before.

You can see the final result in this picture :

In addition, I've uploaded a zip file with all the documents of the solution.

Maybe somebody is gonna need this some day.

Hope it helps!

See you around!