topic Re: Convert Wildcards to Range of Records in QlikView

Convert Wildcards to Range of Records

Thu, 29 Aug 2013 18:38:38 GMT

I have a list of zip codes, some of which are wildcarded with '*'. Each asterisk represents one digit. For example:

9490* equates to 94900 through 94909

949** equates to 94900 through 94999

Example input:

zip
87124
9490*
123**

I would like the output to look like:

zip
87124
94900
94901
...
94909
12300
12301
...
12399

The "...", of course, are all the other zip codes that would be in the table.

Any ideas how I can go about doing this?

Cheers!

Re: Convert Wildcards to Range of Records

whiteline — Thu, 29 Aug 2013 19:50:12 GMT

Hi.

Assuming that wildchars can occur in the end of the zip code, you could convert it like this:

LOAD

zipwildchar,

if(zipwildchar=Previous(zipwildchar), peek(zip)+1, replace(zipwildchar, '*', '0')) as zip

INLINE [

zipwildchar

123**

9490*

87124

]

while iterno()<=pow(10,SubStringCount(zipwildchar, '*'));

Re: Convert Wildcards to Range of Records

Clever_Anjos — Thu, 29 Aug 2013 19:52:46 GMT

yourbase:

LOAD * INLINE [

87124

9490*

123**

];

for i = 0 to NoOfRows('yourbase') -1

let value = Peek('F1', i,'yourbase');

if Index('$(value)','*') > 0 then // your value has at least one '*'

let vStart = Replace('$(value)','*','0');

let vEnd = Replace('$(value)','*','9');

Base:

LOAD

$(vStart) + RowNo() - 1 as Value

AutoGenerate $(vEnd) - $(vStart);

else

Base:

LOAD

$(value) as Value

AutoGenerate 1;

endif

Re: Convert Wildcards to Range of Records

Thu, 29 Aug 2013 20:12:00 GMT

Since I can assume that the asterisks are at the end (no potential of numbers between them), this works perfectly. Thanks!

Re: Convert Wildcards to Range of Records

Thu, 29 Aug 2013 20:22:25 GMT

Hi Clever,

For some reason the loops breaks 123** into a range of 12310 through 12409 instead of 12300 through 12399. The RowNo() in Base doesn't reset with each iteration in the loop. Changing the order of the inline values changes the output with this script. If RowNo() could be reset, this will work.

Re: Convert Wildcards to Range of Records

whiteline — Thu, 29 Aug 2013 20:31:40 GMT

Try to replace RowNo() with RecNo(). The first one counts the resulting table while the second - the source table, the one that is to concatenate in this example.

Re: Convert Wildcards to Range of Records

Thu, 29 Aug 2013 20:36:17 GMT

That worked! Also, needed to add + 1 to the Autogenerate $(vEnd) - $(vStart).

Re: Re: Convert Wildcards to Range of Records

Anonymous — Thu, 29 Aug 2013 20:39:42 GMT

One more solution - see attached

Re: Convert Wildcards to Range of Records

whiteline — Thu, 29 Aug 2013 20:41:15 GMT

It seems that it works only for two wildchars.

Re: Convert Wildcards to Range of Records

Anonymous — Thu, 29 Aug 2013 20:44:02 GMT

Actually this exaample is for one and two. For three, there sholud be additional changes.

(Can place it all in an external loop... )

Re: Convert Wildcards to Range of Records

whiteline — Thu, 29 Aug 2013 21:00:01 GMT

Yeah, I thought about that... There would be another LOAD to determine the maximum number of asterix.

I think the solution with loops could be more flexible.

For example without limitation that the wildchars should be in the end 😃

Re: Convert Wildcards to Range of Records

Clever_Anjos — Fri, 30 Aug 2013 15:58:28 GMT

Hi,

Use Recno() instead, Will fix this