Seperating Information after a blank row

[ryan14]

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?

[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?

[ryan14]

 Position is not fixed and changes each month..

[atcodedog05]

Hi @ryan14 

This is how you can do it

What i am doing here is where ever field 1 is total, domestics or import it marks the start of group and next rows which are not equal to total, domestics or import will take the group of above row. And this is independent of position. Later you can filter it out based on each group.

Hope this helps 🙂

[ryan14]

That works perfectly..

To ask a further question, could that equation of IF [Filed 1] in ("Total","Domestic","Import") THEN [Filed 1]
ELSE [Row-1:Group] ENDIF  be changed to say contains ("Total","Domestic","Import") ?

I scaled down the actual information of the total/domestic/import lines for simplicity but was not sure if this would impact the tool finding the correct term.

if theres not a solution for contains then i can input the full information from the header row

[atcodedog05]

---

@ryan14 wrote:

To ask a further question, could that equation of IF [Filed 1] in ("Total","Domestic","Import") THEN [Filed 1]
ELSE [Row-1:Group] ENDIF  be changed to say contains ("Total","Domestic","Import") ?

---

Hi @ryan14 

For contains to work the logics would be

IF Contains([Filed 1],"Total") or Contains([Filed 1],"Domestic") or Contains([Filed 1],"Import") 
THEN [Filed 1]
ELSE [Row-1:Group] ENDIF

Where each contains is given separately and or is used to check all condition

Workflow:

Hope this helps 🙂