Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!
Free Trial

Forum - Deutsch

Suchen Sie nach Antworten, stellen Sie Fragen und teilen Sie Ihr Alteryx-Wissen.
GELÖST

Adding headers as columns

Hey guys, 

 

I am challenging a certain problem at the moment. I have a report converted from a .pdf to .xlsx. However the PDF contained headers at every single page. What I would like to do is deleting these rows and adding the information from it to every single row related to it. Here is an Example:

 

Account 100Cash
Date Text 
01.01.2000Bank YX
20.03.2000Bank XY
Account 200Inventory
Date Text 
20.03.2000Table
30.08.2000Pencils
31.08.2000Laptops
01.09.2000PC's
Account 300Buildings
Date Text 
01.09.2000Building A
01.09.2000Building B


I would like to look it more like that:

 

AccountAcc. DescriptionDate Text 
100Cash01.01.2000Bank YX
100Cash20.03.2000Bank XY
200Inventory20.03.2000Table
200Inventory30.08.2000Pencils
200Inventory31.08.2000Laptops
200Inventory01.09.2000PC's
300Buildings01.09.2000Building A
300Buildings01.09.2000Building B

 

Is there any possible way to do that in alteryx? Thank you a lot.

4 ANTWORTEN 4
grossal
15 - Aurora
15 - Aurora

Hi @Torben_Wiesbach,

 

welcome to the Alteryx Community and especially to the German Forum! Feel free to speak up in German next time. As the post was asked in English, I'll continue in English.

 

As often, it's definitely possible in Alteryx and doesn't need much tools to do so. I'll show you how it could be done.

 

The workflow looks like this:

grossal_0-1614681852805.png

 

And the result matches yours if I see it correctly:

grossal_1-1614681883287.png

 

Let's take a look how we achieved this:

0. I expect that your data comes in with no header, this makes it easier to handle it. For your real file, just deselect that the first Row contains field names in the option.

grossal_2-1614681945258.png

 

1. Separate the Account Number from the Word "Account". This can be done with a Text-To-Columns tool that splits by whitespace (\s):

grossal_3-1614682035045.png

 

The result afterwards looks like this:

grossal_4-1614682059416.png

 

2. In the next step we want to fill up the account-rows with a Multi-Row-Formula. We can simply say "If the current row is null, than take the previous row, if it's already filled, leave it as it is"

grossal_5-1614682127269.png

 

The result looks a lot better!

grossal_6-1614682148160.png

 

3. We move on with the Acc. Description column, therefore we could use another Multi-Row-Formula:

grossal_7-1614682184616.png

 

This time it looks a bit more tricky, but it's actually easier. We use the Group By option with our newly created field Account. Therefore we can say, if the previous description is null - this happens only at the first entry of every group, we take the value from the Field2 column. If the previous value isn't null, we can just take the previous one.

 

The result looks like this and we are almost done:

grossal_8-1614682287771.png

 

4. Time for some basic renaming and re-ordering. (Select Tool)

grossal_9-1614682316113.png

 

grossal_10-1614682326607.png

 

5. In the last step we remove all Rows with a Filter Tool that contain the Word "Account" or "Date" in the 'Date' Column.

grossal_11-1614682366603.png

 

An easy way to achieve this is using the IN-Funtion that basically checks if a value is in a list. In our case we add 'NOT' in front to remove all lines that contain one of these words.

 

That's it 🙂

 

I'll attach the workflow as a reference for you. Let me know if this helped and yet again, welcome to the forum!

 

 

Best

Alex

 

 

 

 

 

 

 

StephV
Alteryx Alumni (Retired)

Hallo @Torben_Wiesbach,

 

War diese Antwort von @grossal für Sie hilfreich? Wenn Ihnen die Antwort geholfen hat, können Sie diese als „Lösung akzeptieren“. So können auch andere in der Community nützliche Antworten finden.

Danke!

 

Viel Spaß mit Alteryx, bei Fragen sind wir hier im Forum immer gerne für dich da. 

Steph Vitale-Havreng

Hi  @grossal ,

 

danke dir vielmals 🙂 Das hat mein Problem gelöst. In Zukunft werde ich meine Posts auf Deutsch verfassen. 

 

LG

Torben

StephV
Alteryx Alumni (Retired)

Hallo @Torben_Wiesbach , 

 

vielen Dank, dass Sie die Antwort von als Lösung akzeptiert haben.

 

Es freut mich zu sehen, dass die deutsche Community (Danke @grossal 😎) Ihnen helfen konnte.

 

Viel Spaß mit Alteryx, bei Fragen sind wir hier im Forum immer gerne für dich da.

 

Einen schönen Tag,

Steph Vitale-Havreng
Beschriftungen