Use Tile tool with 1 field (some blanks) or 2 fields

[gshah]

Hello everyone,

I am new to the world of Alteryx and need some assistance.

Here is my scenario:

I have a file with Account numbers and Tax ID. Sometimes we may have setup multiple Accounts with same Tax ID and some with Tax ID blank.

I need to group all accounts with same Tax ID as Tile 1 with Sequence number. ==> This works perfectly for all data that has Tax ID.

However where tax ID is blank, it marks all of them as same Tile Number.   <== For this I need either unique tile number for each blank Tax ID record or use Account Number (as an alternate field) to generate unique tile numbers.

Input data and expected results

Input data and expected output

However, what I get as my output is as follows (in orange):

1. It sorts my data. I would like to avoid that if possible.

2. It marks all blanks as same tile after sorting.  Need unique for each blank

Any assistance is appreciated.

regards

Gaurav

[andyuttley · Accepted solution]

Hey @gshah 

Yes, just two things you'll need to do here:

1 - add in an IF statement to populate your blank Tax ID

2 - there is a 'Leave Unsorted' check box, but this (from what I've seen) impacts the logic within the tool (i.e. it'll sort then just check if the row(s) above match), which doesn't give you the output you want. So instead, leave this unchecked (so it still finds later instances) but put a record ID at the start, and sort by that again at the end:

Also attached. Hope that helps

Andy 

[gshah]

@andyuttley

Thanks for the quick response.

that worked!! 😄

I can use the Account number itself at the end for the original sort. (no need for Row ID)

1 more question:

I have the rare possibility that my Tax ID and Account number may end up the same. I want to avoid those ending up as the same Tile.

I tried to add "A" to the Account number column before doing the formula, but I end up with it being string instead of number issue.

Do I need to add a select tool to change the data type or Can I change the datatype here?

thanks

[andyuttley]

No problem @gshah - pleased it worked.

Yes, if you're adding an "A" to the start of [Account Number] you'll need to make sure it's a string first. Likewise, if your [Tax ID] references this field (when it takes on Account Number if it's NULL), then this will also need to be a string.

A select straight after the input will sort that, like you thought. Let me know if not 

Andy

[gshah]

Select at the beginning and converting to String worked perfectly.

The TILE tool does not care whether it is string or number for grouping.

thanks