Tile Tool

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

Tax_Info.xlsx

Datasets

Accepted answers

apathetichell

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

california sales tax project.yxzp

All comments

apathetichell

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

california sales tax project.yxzp

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.

apathetichell

you don't even need regex for that you can use

if contains([location]) then replace([location],"County","-") else [location] endif

in a formula tool to update the locatoin field.

knnwndlm

@apathetichell - I need to use RegEx because there are locations that have the word "County" in it. I only want the ones that have Unincorporated within the same string to have the word "County" replaced with "-".

apathetichell

Yes -that's what the IF statement does.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280