Split Number Ranges to WildCard Characters

gayulokesh

Hi, 

I am working on an input data set which has number ranges like 

42110 - 99999 

I am using generate rows to create number range to individual row which creates 57890 rows, instead I would like to create rows with wildcard where possible like below. Any scalable solution to handle this please ? 

4211* 

4212*

4213*

4214*

4215*

4216*

4217*

4218*

4219*

422**

423**

424**

425**

426**

427**

428**

429**

43***

44***

45***

46***

47***

48***

49***

5****

6****

7****

8****

9****

Regards,

danilang

Hi @gayulokesh 

This was a fun one to start the morning

The trick here is to split the start value into its digits using the Regex Parse tool and then treat each digit as the start of it's own wildcard group.  Get the Max_RecordId.  This is used to remove to "ones" group since it's included in the 1st wildcard batch and also in the initialization expression in the MultiRow tool.  For all the wild card groups initial=[value+1] except for the last one where it's just [Value].  The formula that creates the final value is interesting as well because it builds up the final number by using string operations on the initial one

tonumber(substring(tostring([value]),0,[RecordID]-1)+"9")

 At the end of the day your results look like this

Note that this example assumes that your ranges all end on the "9".  If you need custom end values like 42110 - 52000, you should be able to modify it by splitting the end value to digits and joining this to the start digits.  In the final value formula, replace the "9" with the split digit

Dan

WildCardRanges.yxmd