Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Convert Wildcards to Range of Records

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!

Tags (1)
1 Solution

Accepted Solutions
whiteline
Honored Contributor II

Re: Convert Wildcards to Range of Records

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

11 Replies
whiteline
Honored Contributor II

Re: Convert Wildcards to Range of Records

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

Employee
Employee

Re: Convert Wildcards to Range of Records

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

Not applicable

Re: Convert Wildcards to Range of Records

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

Not applicable

Re: Convert Wildcards to Range of Records

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
Honored Contributor II

Re: Convert Wildcards to Range of Records

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.

Not applicable

Re: Convert Wildcards to Range of Records

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

mov
Esteemed Contributor III

Re: Re: Convert Wildcards to Range of Records

One more solution - see attached

whiteline
Honored Contributor II

Re: Convert Wildcards to Range of Records

It seems that it works only for two wildchars.

mov
Esteemed Contributor III

Re: Convert Wildcards to Range of Records

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

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

Community Browser