Generate new row and copy data from specified row above based on condition

Hi guys, I'm new to Alteryx and couldn't figure out a way how to do this, maybe someone can help.

I have an input table that looks like the following:

Location (header)

Berlin

Munich

London

I'd like to generate a new row whenever there's a new location (active row does not equal row -1) and copy the data from the first row (header). The Output should look like this:

Location

Berlin

Location

Munich

Location

London

It would also be necessary to save every location in a new excel sheet (within the same workbook).

Thanks and kind regards!

Workflow

Accepted answers

ShankerV

Hi @snwk

Step 4: Formula tool

IF [Temp]="1"
THEN "2nd header"
ELSE [Temp]
ENDIF

Step 5:

Step 6:

Many thanks

Shanker V

BS_THE_ANALYST

@snwk I've made the solution dynamic now - thanks to @ShankerV 's workflow/logic. You can select whatever column name you like, and aslong as there's a subheading, this workflow will arrange your data into groups (they may be mixed up i.e. London, Berlin, London, London), then output what you required.

Thanks for the question, this was a great learning for me! 😀

190123help2_dynamicworkflow.yxmd

All comments

ShankerV

Hi @snwk

If your end goal is to save the city names in different files, there is no need to populate the Location eachtime.

The header will be populated in the excel output.

ShankerV

Hi @snwk

Please find the expected output.

Input was:

ShankerV

@snwk

Output tool settings:

Many thanks

Shanker V

snwk

Sorry, I forgot to specify this: There are basically 2 header rows, not 1. I managed to save it in different sheets, but of course the "second" header row is missing.

Location (header)

2nd header

Berlin

Munich

London

ShankerV

Hi @snwk

So you need both header in your output sheet?

Or only the 2nd header is enough in the output sheet.

snwk

Yes, exactly

ShankerV

Hi @snwk

One way of doing this.

Many thanks

Shanker V

ShankerV

Hi @snwk

Step 1: Input

Step 2:

Step 3:

IF [RecordID]<=2
THEN 0
ELSEIF [Location]=[Row-1:Location]
THEN 0
ELSE 1
ENDIF

Many thanks

Shanker V

ShankerV

Hi @snwk

Step 4: Formula tool

IF [Temp]="1"
THEN "2nd header"
ELSE [Temp]
ENDIF

Step 5:

Step 6:

Many thanks

Shanker V

ShankerV

Hi @snwk

Step 7: Filter tool

Step 8:

Expected output:

Many thanks

Shanker V

BS_THE_ANALYST

One way to rearrange the data:

From this:

to this

The workflow depends on the header being called 'Location' though. If you change it, the workflow will prompt you to tick the boxes for the new header name.

190123help2.yxmd

snwk

Thank you, it worked!!

BS_THE_ANALYST

Thanks for the question, this was a great learning for me! 😀

190123help2_dynamicworkflow.yxmd

ShankerV

Hi @BS_THE_ANALYST

Thanks. Good to know my workflow/logic helped you too....

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703