Highcharts - Cannot access multiple levels of data in nested Json map

Question & Answer
,
1

Hi,
I have a dataset where I want to extract multiple series for a line chart following the templates in the Demo central - https://democentral.learnquicksight.online/#Dashboard-FeatureDemo-Highcharts-Visual.
I have a dataset with 9 columns, where 4 of them are included in my visual.
If I hard code the value for the filter, I get the “correct” data for one of the series (but this value for all series as expected for this hardcoding).

I cannot find a way to access the value for the name of “outer” series to get the correct data for each series.
The example online suggests using the [“item”, 1], but that gives me no data.

A snippet of my code is:
“series”:
[“map”,
[“unique”, [“getColumn”, 3]], // One series per order
{
“name”: [“item”],
“data”: [“map”,
[“filter”, [“getColumn”, 0, 1, 2, 3], [“==”, [“get”,[“item”], 3],
[“item”, 1] // This does not work for me to get the name of the series
//“136737320” - this “works” to get the data for a single series shown in attached image
//[“name”] - Also tried to get the series name to find the matching data
//“NRD B 342195 712” - this “works” for another column
] ],
[[“get”,[“item”],1], [“get”,[“item”],2]]
],

I am trying to achieve a “Scatter chart” kind of visual where each data series is a short line between two pairs of xy coordinates.

image
image838×550 63.5 KB

Bonus question:
Is there any other way to achieve this with QuickSight visuals?

2

Hello @magnus.kling

The first thing I can think to try is adding the x and y coordinates. Looking at your code snippet everything looks okay so I’m curious if its just mapping. Can you try the following:

“series”:[
	“map”,
	[“unique”, [“getColumn”, 3]],
	{
		“name”: [“item”],
		“data”: [
			“map”,
			[
				“filter”, 
				[“getColumn”, 0, 1, 2, 3], 
				[“==”, [“get”,[“item”], 3],[“item”, 1]]
			],
			"x": [[“get”,[“item”],1], [“get”,[“item”],2]]
			"y":[[“get”,[“item”],3]] //this is a placeholder value,you can update this to something that makes sense to your visual
			],

Let me know if this works/throws an error/doesnt make a change

3

Thanks for the suggestion, but that does not work for me.
From my snippet,

[["get",["item"],1], ["get",["item"],2]]

will “translate” into:

[x-ccord, y-coord]

which is just fine. And I get some error messages trying to assign the x/y coordinates the way you suggested.

The problem is that I cannot access the outer level name of the series in the inner loop.

I am getting the correct names for each of series in the map using:
["unique", ["getColumn", 3]]

This will create the number of series that I expect, with the names as I expect. One of them is called “136737320”.
If I use this series name hard coded in the inner loop, all my series will plot the same line for the values in this series. If I change this hard coded value to another series name, I get that line plotted for all unique series. You can see in the image that all different markers are available at each end of the plotted line.

But what I really want is to filter each of the series using that individual name to plot that series in its color, and I cannot understand how do get this using this in the inner loop.
[“item”, 1]
It seems like this value is no longer accessible when the new loop is started.

It would be nice if I could assign a variable in the outer loop to be used in the inner loop:

“series”:
 [“map”,
  [“unique”, [“getColumn”, 3]], // One series per order
  {
   “var_currentSeriesName”: [“item”], // Variable to be used as the series name, and in the inner loop filter.
   “name”: [“var_currentSeriesName”], // Set the name of the series
   “data”: [
    “map”,
     // Include data only for the current series
     [“filter”, 
      [“getColumn”, 0, 1, 2, 3], [“==”, [“get”,[“item”], 3], [“var_currentSeriesName”] ] ], 
      [[“get”,[“item”],1], [“get”,[“item”],2]] // [ x-coord, y-coord] for each data point
  ],
4

Edit: No longer relevant. See below post.
The main problem is still that I cannot access the series to run the inner map per series.

I could find a way to set the x/y values similar to what was suggested:

    "series":
    ["map",
        ["unique", ["getColumn", 3]], // One series per order
            {
                "name": ["item", 1],
                "data": ["map",
                    ["filter", ["getColumn", 0, 1, 2, 3], [
                        "==", ["get",["item"], 3],
                        //["item", 3] // This does not work for me to get the name of the series
                        "136737306" //this “works” to get the data for a single series shown in attached image
                        //["name"] - Also tried to get the series name to find the matching data
                        //"NRD B 342195 712" // this “works” for another column
                        ]
                    ],
                    //[["get",["item"],1], ["get",["item"],2]],
                    { 
                        "x": ["get",["item"],1], 
                        "y": ["get",["item"],2]
                    }
                ]
            }
    ]

However, I could not get this to work with a pair of coordinates.

I also tried the Vector chart type to plot each pair of coordinates as a vector, but this type of chart is not available in AWS Quicksight.

5

Solution found, but I am still not understanding how this works…

The correct code should be:

    "series":
    ["map",
        ["unique", ["getColumn", 3]],
            {
                "name": ["item", 1],
                "data": ["map",
                    ["filter", ["getColumn", 0, 1, 2, 3], [
                        "==", ["get",["item"], 3],
                        ["item", 2] // ???
                        ]
                    ],
                    { 
                        "x": ["get",["item"],1], 
                        "y": ["get",["item"],2]
                    }
                ]
            }
    ]

I have data in four columns:

  • Description
  • X
  • Y
  • Series name

Mapping the unique last column (index = 3) gives me the series’ names, which I can access as: ["item", 1]

Retrieving the x/y values for each series is now done by creating a new map where I get all four columns and filter the original data per series name.
To filter this data, I have to compare the column with index 3, with: ["item", 2] to get the data for each individual series.

I have not yet figured out where this index 2 comes from…

6

@magnus.kling ,

Here is my example when I was testing and to your point on the index , I understand for map it starts with 1 Note: array index is 0-based but map index starts at 2

image
image1622×574 98 KB

Kind regards,
Koushik

7

Hi Koushik @koushik, @magnus.kling,

Adding some more explanation -

Correct. The index is optional here. You can reference it as [“item”] as well. This means item that is from the current context - which in your case is the unique Series name being iterated over currently in the outer map operator. Note that this index starts at 1. ie - [“item”] and [“item”,1] is the same - They both refer to item from current context.

You are pulling all four columns into the filter operation with ["getColumn", 0, 1, 2, 3].
You want to filter the rows matching the Series name that is being iterated in the outer map.
So, the filter condition gets specified as ["==", ["get", ["item"], 3], ["item",2] ].
As you correctly figured out, the ["get", ["item"], 3] is picking the Series name field - as ["item"] here is each row from ["getColumn", 0, 1, 2, 3], and as get uses a 0 based index.
You want to pick only the rows that match the Series name that is being iterated in the outer map. That is why the second parameter in the == operator is ["item",2]
["item",2] means the item from the parent context - which is the unique Series name being iterated in the outer map.
If there are further nesting of functions, you can reference items from grandparent or great-grandparent contexts as ["item",3] and ["item",4] respectively. Hope this clears the confusion on why ["item",2] was required in the code snippet.

Regards,
Arun Santhosh
Pr QuickSight SA

1 Like