Seperating Information after a blank row

Data comes in via an excel file. The first 10 lines are the total(domestic plus import) then 2 blank lines, then 10 lines with the domestic amount, then 2 blank lines, then 10 lines with the import total.

How would i go about putting an identifier onto the 30 lines with amounts saying if the line is from the total, domestic, or import with only uploading the initial file rather than separating the info onto different tabs?

Input

Accepted answers

All comments

atcodedog05

Hi @ryan14

If the position is fixed you can use select records tool for this scenario to split data.

https://help.alteryx.com/current/designer/select-records-tool

Video : https://www.youtube.com/watch?v=5ZpUjOHZjdM

If the position is not fixed. Can you provide a sample file to work on.

Hope this helps 🙂

BrandonB

Is it always the same number of rows? An easy solution would be to add a Record ID tool to number each row and then in a formula tool create a new column that says if row number <= 10 then "Domestic", and so on and so forth. Otherwise I would suggest taking a multi-row tool approach that looks to see if the column above it is null so that it could be flexible if your number of rows change.

atcodedog05

Hi @ryan14

Another thing also that you can do is set record id using record id tool. Then filter record ID as per the needed range.

Hope this helps 🙂

ryan14

within the multi row tool how would i label the areas by there type? I understand how to use the +1, -1 part of the row aspect, but dont understand how to tell the tool when to change the output of the equation to the different types?

Example Total Domestic Import.yxmd

ryan14

Position is not fixed and changes each month..

Example Total Domestic Import.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703