topic Split Number Ranges to WildCard Characters in Alteryx Designer Desktop Discussions

Split Number Ranges to WildCard Characters

gayulokesh — Wed, 10 Mar 2021 04:02:21 GMT

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,

Re: Split Number Ranges to WildCard Characters

danilang — Wed, 10 Mar 2021 13:47:28 GMT

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

Re: Split Number Ranges to WildCard Characters

gayulokesh — Thu, 11 Mar 2021 02:21:34 GMT

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

Re: Split Number Ranges to WildCard Characters

danilang — Thu, 11 Mar 2021 14:00:43 GMT

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

Re: Split Number Ranges to WildCard Characters

gayulokesh — Thu, 11 Mar 2021 15:00:07 GMT

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

Re: Split Number Ranges to WildCard Characters

danilang — Thu, 11 Mar 2021 15:30:33 GMT

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

Re: Split Number Ranges to WildCard Characters

gayulokesh — Thu, 11 Mar 2021 17:26:13 GMT

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 ?

Re: Split Number Ranges to WildCard Characters

Qiu — Fri, 12 Mar 2021 05:56:00 GMT

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

Re: Split Number Ranges to WildCard Characters

gayulokesh — Fri, 12 Mar 2021 12:25:53 GMT

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.

Re: Split Number Ranges to WildCard Characters

Qiu — Fri, 12 Mar 2021 12:32:35 GMT

@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.

Re: Split Number Ranges to WildCard Characters

Qiu — Sun, 21 Mar 2021 11:43:30 GMT

@danilang

Sorry for taking long.

I modified the macro to introduce the additional logic you have requested.

Based on my test, it works.

Of course if you have new, we have to modify again maybe. but perhaps not? 😁