Convert Wildcards to Range of Records

[#1]

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!

[whiteline · Accepted solution]

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, '*'));

[Clever_Anjos]

yourbase:

LOAD * INLINE [

    F1

    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

next

[#4]

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

[#5]

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.

[whiteline]

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.

[#7]

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

[Anonymous]

One more solution - see attached

[whiteline]

It seems that it works only for two wildchars.

[Anonymous]

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

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