Multi-Row assigning values with length formula

[juan1]

Dear All,

I am having a tough time trying to group Cities, Countries, and Airport Codes from the same column. These were downloaded from a webpage and I scrapped the values into the format that I wanted. However, I am trying to group them by line instead of having them all in the same column. I am trying to use the multi-row formula for this, so that then I can use the Cross Tab tool.

My idea is to assign a value of 0's to the data other than the airport code (3-digit code) And then use another multi-row formula with +1 to have increasing number by every group of three. Meaning group 1 is 1, group 2 is 2, etc.

Maybe this is a hard way of solving it, but I ran out of ideas.

Thank you so much in advance for your help! 🙂

Best,

Juan1

[Luke_C · Accepted solution]

Hi @juan1 

Here's three different ways to do this:

1. Make columns tool: Creates columns based on one column, in this case I specified every three fields would correlate to the columns. It's still technically a 'laboratory' tool though, so no guarantee there won't be bugs. It seems to work well though.
2. Tile Tool: Using the equal records option, I can group the data into different tiles and crosstab here. You'll need to divide your total records by 3 to get the number of tiles to enter. (i.e. I tested with 4 airports which is 12 records, so I entered 4).
3. Multi-row formulas: I used this to emulate the tile tool, but would be dynamic if the the # of records is changing between runs. It checks if the mod of the record ID is 0 to define each new airport. 

[DawnDuong · Accepted solution]

Hi @juan1 

One way is to use the 2 formula tools and a cross tab tool, as shown in the mock-up below.

Cheers,
Dawn.