Rotating (transposing) the table

[Jean-Yves Le StangParticipant]

Good Day, gents

I have created a table were dates are rows and country names are columns, giving one variable as a function of date and country). Now I would like to select the last row of the table (I know how to determine the index) and turn it, so that the country names turn to one column and their last value an adjacent column.

I can do it using copy and paste/special with “transpose” enabled but I would like to do it dynamically.

Basically I can create a separate column with country names but I don’t know how to populate an adjacent column with the last values corresponding to each country name.

I have made several unsuccessful attempts using a pivot table ?

Can you help ?

VMT in advance

[dgteamModerator]

First, to make sure I understand your question ….

Sounds like you have data that looks like this and you want the last row in a column.

Assuming I have that correct, there is a way you can pull the last values using Global variables.

First, use a Number from column variable, to pull the last row number.

Then set up a Number from column variable for each column that uses that row number.

NOTE: Once you create one of these, you can select the variable object, use Command-C to copy, Command-V to paste, and then edit for the next column.  That makes it a lot faster.

Once, the variables are created, refer to them in the data table.

For example, if you have a text column with the name of the country …

Use the gear menu to convert to a number column and change the display to show the value of the variable.

The column ‘Last’, shown above, can used just like any other number column and will update dynamically as your data changes.

Note you can also change the value of the variable ‘Row’ anytime to change the data pulled for all the columns.  Maybe even make it a slider so you can dynamically change the row that is selected.

In terms of setting this up, clearly this could be very tedious if you have a lot of countries.

In that case, consider converting the format to a Flattened data set.  Then you can use the Pivot command to filter and query the data.

Hope this helps!

[Jean-Yves Le StangParticipant]

This exactly what I expected and I got the result I was looking for using the first method… not to much tedious… I was surprised that turning a text column into a number column would provide the result!

Regarding the second method, I could not flatten data (see attached picture, the “Flatten Columns” menu was not enabled – and you can guess my target of interest !) may be because they are elaborated through a ten row average ? I have reviewed the video on your website to make sure I was doing it well but no success.

Many thanks for your prompt reply.

 

 

[Author]

Posts