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

[gayulokesh]

Thank you very much for your help on this. I had thought I understood the logic you have used, but when I try to expand the scope for other input data like you have mentioned I am not able to get the desired results . 

Can you share how you will build the logic for 

input data range like 

1. 42110 - 52000 

Other applicable input data  scenario will be like 

2. 42101-42106

we should get 7 rows with each on the number in the range  with no wild card padding 

3. 91901-91901 

we should get one row as the transformed record 

4. 00000 - 42101 

This is another applicable data set that our logic should be able to handle

I appreciate your help, very much appreciated. 

Regards,

Gayathiri

[danilang]

Hi @gayulokesh 

Ah  The joy of getting new requirements after you've finished the project.

Can you post the changes that you've made to the initial workflow to handle the new cases and I'll help you out 

Dan

[gayulokesh]

HI @Danilang, 

Please see below the updated workflow. 

I am not sure on how to build the Start Value and Final Value for this combination like 

42110 - 52000 

In the final value formula logic , I've built like this 

4 5( 1st digit from end ) 
42 42 (4+ 2nd digit from end ) 
421 420 (previous value plus 3rd digit from end ) 
4211 4210 ( previous value plus 4th digit from end ) 

But this doesn't seem to work as well. 

Thanks again for your help . 

Would like to see our logic work for other ranges like 00000- 42000 , 91901 - 91901 

Thanks

Gayathiri 

[danilang]

Hi @gayulokesh 

Start off by doing what I suggested in previous reply(in bold below) Join on record position

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

with this you'll have the final digit for each position.  Change the Final value formula from the original workflow to use this digit instead of the nine.  After this you should be able to remove the rows where the end digit is the same as the start before the generate rows

Dan

[gayulokesh]

Thank you again for your time on this , after you spilt the end string by rows . Do we join "start digit split" and "end digit split" based on record position ? 

On the final value formula we have something like this now 

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

On the Pad wild card, I have used this 

PadRight(tostring([Wild]),[Max_RecordID],
[Right_End])

This isn't helping me to get the desired results, sorry I am missing on what you are suggesting here. I've attached what I have updated for your reference. Can you please share your thoughts ? 

[Qiu]

@gayulokesh 
I use the Displacement approach, and its more mathmatical.

[gayulokesh]

Thank you Qui for your time on this. When I change the data range to like 

42500 - 62500 , wild card build isn't correct. Can you please check on how we could make this work for all the data range. I will also check on my end based on the lead from your approach. 

[Qiu]

@gayulokesh 
Sorry about that.

@danilang also let me know that there are other cases need to be considered.

I believe we have to add a bit more conditions into it.
Let me take a look.