Transpose from multiple column,

[Mirco_Kunz]

Hello together

Maybe someone can help me. I've already tried transpose and cross-tab, but I can't get the right end result.

The following starting position:

Date	Ward	Patients	Beds	Ward	Patients	Beds	Ward	...
01.01.2019	540	1	10	520	1	8	408	14
02.01.2019	540	3	10	520	1	8	408	15
03.01.2019	540	5	10	520	1	8	408	13

There is an Excel sheet with the bed occupancy of a clinic.

They have one row per date and then simply filled in the name of the department in the Ward columns. Then the number of patients on that day and then how many beds they had open.

For this client this is done over several columns, a total of almost 12 departments.

Because this is a very difficult representation to analyse, I would like to have it in the following representation:

Date	Ward	Patients	Beds
01.01.2019	540	1	10
01.01.2019	520	1	8
01.01.2019	408	14	...
02.01.2019	540	3	10
02.01.2019	520	1	8
02.01.2019	408	15	...

How do I do that?

I have attached a sample file

[ChrisTX]

These posts provide options for a dynamic Transpose:

Dynamic Transpose Key Field + Summary Field
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Transpose-Key-Field-Summary-Fi...

List box with Transpose
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/List-box-with-Transpose/td-p/514539

Chris

[WernerE]

Hi @Mirco_Kunz 

You should be able to achieve the desired result with the attached and add as many departments as you like.

Let me know if you have any questions. 

[JP_SDAK]

I started this before taking my daughter to swim this morning.  Decided to go ahead and finish and post (mostly as an exercise for myself  since  WernerE   already gave a much more elegant example😀)

[Qiu]

@Mirco_Kunz 
This one will work for even 12 departs.