Choose Language Hide Translation Bar
Highlighted
ezorlo
Level III

match values ordering in two data tables

Dear Community,

I have two data tables and I want to use the same value ordering for a certain variable that appears in both. I used "row order levels" option for dt1. How can I extract the value order and easily apply it to dt2?

Thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: match values ordering in two data tables

  1. Create a new column in the data table that you want to know the sort order for and set it's formula equal to
    Row()
  2. Use Tables==>Summary specifying the column in the data table you have Row Level Ordering for as the Grouping column, and for the statistical column, specify Min for the column that has the row() formula specified for
  3. Update the data table you want to set the ordering on, with the summary data table, matching on the Grouping column.
  4. Sort the updated table by the column that has the Min row number
  5. Apply row level ordering to the column in the updated table that you want to have the same order  for
Jim

View solution in original post

2 REPLIES 2
Highlighted
txnelson
Super User

Re: match values ordering in two data tables

  1. Create a new column in the data table that you want to know the sort order for and set it's formula equal to
    Row()
  2. Use Tables==>Summary specifying the column in the data table you have Row Level Ordering for as the Grouping column, and for the statistical column, specify Min for the column that has the row() formula specified for
  3. Update the data table you want to set the ordering on, with the summary data table, matching on the Grouping column.
  4. Sort the updated table by the column that has the Min row number
  5. Apply row level ordering to the column in the updated table that you want to have the same order  for
Jim

View solution in original post

Highlighted
ezorlo
Level III

Re: match values ordering in two data tables

Jim,

You are a wizard! Thank you so much.

As some background, I want to plot the ratio of means as a heat map alongside the actual means including st dev error intervals.

So far the only way I can do this is to make a separate summary table of just the means, make a third column of ratio and use the ratio as color. In order to make the heat map easy to read I also sort the rows according to ratio, that way there is a continuous gradient

However When I go back to the original table with all the data in order to make line graph with std dev error bars, i am left with a real mess for the ordering!

This solution worked!

Now if I could only figure out how to add a ratio column to the original data table based on a grouping variable and then use the average ratio as a color.... that may be even better so I could plot the means and a heat map of ratio side by side

Best, 

 

Article Labels

    There are no labels assigned to this post.