Forum Discussion
Relationships Between Rows and Colums
I have two data sets:
1. Geographic location table which has three columns: Lat, Long, UID. I have used this to generate a map using the ArcGIS visualisation tool.
2. A table which contains a time versus quantity series for each UID. This has several hundred columns; one for time, and one for each UID.
See example:
I would like to be able to select the UID in the ArcGIS window and by doing so, bring up a plot of the time series data for that node. My issue is that the UID is in rows for the ArcGIS table and columns for the time series data.
Is there a way of linking the two?
Many thanks,
Claire
5 Replies
- dm-p
Super User
Based on how I interpret it, I'd do the following. note that as your data is an image, I've manually entered a small portion of it, but the principle should be the same.
- Create a query for your coordinates/UIDs (we'll call this UID), e.g.:
- Create another query for your second table (we'll call this Time)
- Once the data has been loaded, highlight the Time column, select Unpivot Data and Unpivot Other Columns, e.g.:
- This will now create one row per time per UID, e.g.:
- Rename the Attribute column to UID.
- Cchange the data type of the Value column to Decimal Number and then Close & Apply.
- Join the UID table to the Time table on the UID column (Power BI may well have already done this for you), e.g.:
- I created a line chart, with Time on the Axis, Value under Values, and UID (from the UID table, although it shouldn't matter) for Legend. This will now cross-filter when clicking on a coordinate in the ArcGIS visual, e.g.:
This may not be exactly what you're after but might get you close to where you need to be?
Thank you very much for your reply - really appreciate your time. I followed all your steps and got exactly what I wanted. However, one thing I did notice was that if I use my full data set which has 733 columns for the original time series, I got an error saying that the relationships could not be created. I then trimmed the data so that I only had 24 columns and it worked perfectly. Are you aware of any kind of data size limit for this method?
Claire
As a follow up to this, I have tested some different sized data sets with UIDs which are different from those in the dataset which worked yesterday and I get an error even with 20 columns so I am now wondering whether there is some specific issue within the data set.
The specific error is:
Column 'Time' in Table 'Time' contains a duplicate value '80.583' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
I have checked that all my UIDs are unique. The 'Value' column does not contain unique values but then this was also the case for the dataset which worked!
Many thanks,
Claire
- Create a query for your coordinates/UIDs (we'll call this UID), e.g.:
Good day, I have a issue that relates to this tread however when applying this recommendation, then it disrupts the rest of my measures. When you unpivot a column then it duplicates the line entry. Are there any other options to achieve the same result? See below
The objective is to create a radar chart. The issue is that the dimension / category does not exist in the main table but there is a outcome value for the category in the main table. I have created a second table with the category but not able to map the row and the column. Any suggestions?
