topic Wildmatch in Intervalmatch in QlikView

Wildmatch in Intervalmatch

Tue, 11 May 2010 09:29:42 GMT

Hi Guys,

We've got a unique requirement whereby we need to interval match on a wildcard range.

For example,

GLAccountFrom	GLAccountTo	GLGroupDescription
1???????3000	1???????3080	Sales
1???????3500	1???????3580	Cost of Goods Sold

What is the simplest and easist way to do this?

The GL Account (e.g. 101001003000) would need to match to the 'Sales' GLGroupDescription by 'wildcarding' the question marks in the interval match.

Thanks.

Wildmatch in Intervalmatch

johnw — Tue, 11 May 2010 23:34:56 GMT

Are the wildcards always in the same positions? If so, you could split the other digits into a separate field. So you have 13000 through 13080 as "Sales". Then it's a matter of generating the whole range, probably with a WHILE loop and iterno().

Wildmatch in Intervalmatch

rwunderlich — Wed, 12 May 2010 00:52:40 GMT

I can think of two Qlikview Cookbook examples that might help you.

"Mapping numeric ranges"

"Mapping with a table using wildcards"

-Rob

Wildmatch in Intervalmatch

Wed, 12 May 2010 02:22:37 GMT

John,

Unfortunately they can be in any position and may occur within more than one range.

e.g. 10??100??3800

Wildmatch in Intervalmatch

johnw — Thu, 13 May 2010 00:45:03 GMT

Then perhaps handle it with a combination of the "mapping with a table using wildcards" in the QlikView cookbook and a while loop with iterno(). So if your table has 10??100??3800 10??100??3850, iterate to generate all values in the range, and then use the wildcard mapping approach handle the rest. I can try to assemble an example if you have trouble with it. Sounds like an interesting problem.