Tile Tool

[knnwndlm]

Hi SME,

Could somebody please help me figure out how to use the Tile tool to generate the Results tab (columns E & F) based on the initial info on the Gov_Table tab?  Essentially, what I'm trying to do is to group all the Counties together and within each county, to label Tag1 with incremental numbers to represent the changes in the County name and Tag2 to indicate the changes in the rates starting with the Type showing County as 0.  What Tag2 allows me to peg all the cities with the same rates with the same numerical value.

I tried to use the Tile tool to do this without avail.  I could do this the long way, but I'm trying to figure out best practice to do this.  

Appreciate your help.

Thanks,

kwl

[apathetichell · Accepted solution]

I was going to try to describe how to do this - but it was quicker to just build it. Had to filter for the unincorporated naming convention...

[knnwndlm]

Thank you @apathetichell !

[knnwndlm]

Hi @apathetichell ,

Could you please help me understand the Filter formula of GetWord([Location],CountWords([Location])-1)="County"?  Also, how can I modify the workflow to remove any cities that have the same tax rate as the county's but leave the county's row intact?

Thanks,

kwl

[apathetichell]

Sure! - I initailly used the fairly straight forward contains([Location],"County") - expresion there - but it also flags unincorporated areas. I needed to figure out a strategy to only get the Counties since I need them for part 1 - and for their base tax rates for part 2. 

There are a slew of ways I could have filtered for these - my usual pick would have been:

regex_match([Location],".*County$") 

and the most straight forward would have been right([Location],6)="County"

I went for count the number of words - and then choose the last word - and see if that is equal to County. Only issue is that one of these functions uses the pythonic 0 for 1, while the other uses the standard first item is 1 context. The -1 just adjusts between the two numbering systems.

Hopefully you're sticking to indirect tax in California - if you start looking at New York - you'll have to go by zip code because of the MTA... at least that's my memory of it...

[knnwndlm]

Hi @apathetichell ,

Could you please help me understand why the RegEx tool would not generate the same results as the Filter tool with the regex above? 

Thanks,

kwl

[apathetichell]

@knnwndlm - it definitely would! I just use Regex so often I wanted to play with some other string functions. You could also use contains([Location],"County") and !contains([Loation],"unincorporated") but that seems too hard coded.

[knnwndlm]

@apathetichell - I see what happened.  with the RegEx tool, I would have to look at the Match column to filter out the False/True to get the data.  Thank you again!

[apathetichell]

I'd just throw the regex_match function in the filter tool. you can use regex_match([Location],".*County$") 

[knnwndlm]

@apathetichell - How do I use RegEx to replace the word "County" with a "-" for any locations that have "Unincorporated" in the String?  I do have other locations that have "County" in the string that I do want to leave alone.